<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
	<title>SQLAlchemy 0.4 Documentation - Mapper Configuration</title>
	
    <link rel="stylesheet" href="style.css"></link>
    <link rel="stylesheet" href="docs.css"></link>
    <link href="syntaxhighlight.css" rel="stylesheet" type="text/css"></link>
    <script src="scripts.js"></script>



</head>
<body>








<div id="topanchor"><a name="top">&nbsp;</a></div>


<h1>SQLAlchemy 0.4 Documentation</h1>

<div id="pagecontrol"><a href="index.html">Multiple Pages</a> | <a href="documentation.html">One Page</a></div>

<div class="versionheader">Version: 0.4.8   Last Updated: 10/12/08 13:33:19</div>







<A name=""></a>


    <div class="topnav">

    
    <div class="navbanner">
        <a href="index.html" class="totoc">Table of Contents</a>
        
    <div class="prevnext">

            
            Previous: <a href="sqlexpression.html">SQL Expression Language Tutorial</a>

               |   
            Next: <a href="session.html">Using the Session</a>
    </div>

        <h2>Mapper Configuration</h2>
    </div>

	
	
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_mapper">Mapper Configuration</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_mapper_columns">Customizing Column Properties</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_deferred">Deferred Column Loading</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_expressions">SQL Expressions as Mapped Attributes</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_overriding">Overriding Attribute Behavior with Synonyms</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_composite">Composite Column Types</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_orderby">Controlling Ordering</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_inheritance">Mapping Class Inheritance Hierarchies</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_mapper_inheritance_joined">Joined Table Inheritance</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_mapper_inheritance_joined_querying">Polymorphic Querying Strategies</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_inheritance_joined_optimizing">Optimizing Joined Table Loads</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="mappers.html#advdatamapping_mapper_inheritance_single">Single Table Inheritance</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_inheritance_concrete">Concrete Table Inheritance</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_inheritance_relations">Using Relations with Inheritance</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="mappers.html#advdatamapping_mapper_joins">Mapping a Class against Multiple Tables</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_selects">Mapping a Class against Arbitrary Selects</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_multiple">Multiple Mappers for One Class</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_mapper_extending">Extending Mapper</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="mappers.html#advdatamapping_relation">Relation Configuration</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_patterns">Basic Relational Patterns</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_patterns_onetomany">One To Many</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_patterns_manytoone">Many To One</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_patterns_onetoone">One To One</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_patterns_manytomany">Many To Many</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_patterns_association">Association Object</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_selfreferential">Adjacency List Relationships</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_selfreferential_query">Self-Referential Query Strategies</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_selfreferential_eagerloading">Configuring Eager Loading</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_customjoin">Specifying Alternate Join Conditions to relation()</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_customjoin_fks">Specifying Foreign Keys</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_customjoin_properties">Building Query-Enabled Properties</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_customjoin_multiplejoin">Multiple Relations against the Same Parent/Child</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_collections">Alternate Collection Implementations</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_collections_custom">Custom Collection Implementations</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_collections_decorators">Annotating Custom Collections via Decorators</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_collections_dictcollections">Dictionary-Based Collections</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_collections_adv_collections">Instrumentation and Custom Types</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_strategies">Configuring Loader Strategies: Lazy Loading, Eager Loading</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_strategies_combining">Combining Eager Loads with Statement/Result Set Queries</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_largecollections">Working with Large Collections</a></li>

	        <li>
                
    <ul>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_largecollections_dynamic">Dynamic Relation Loaders</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_largecollections_noload">Setting Noload</a></li>

        
        <li><a style="" href="mappers.html#advdatamapping_relation_largecollections_passivedelete">Using Passive Deletes</a></li>

    </ul>

	        </li>
        
        <li><a style="" href="mappers.html#advdatamapping_relation_mutablepks">Mutable Primary Keys / Update Cascades</a></li>

    </ul>

	        </li>
    </ul>

	</div>











    
    <A name="advdatamapping"></a>
    
    <div class="sectionL1">

    
    

<p>This section references most major configurational patterns involving the <a href="sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_mapper">mapper()</a> and <a href="sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_relation">relation()</a> functions.  It assumes you've worked through the <a href="ormtutorial.html">Object Relational Tutorial</a> and know how to construct and use rudimentary mappers and relations.
</p>


    
    <A name="advdatamapping_mapper"></a>
    
    <div class="sectionL2">

    <h3>Mapper Configuration</h3>
    
    

<p>Full API documentation for the ORM:
</p>
<p><a href="sqlalchemy_orm.html#docstrings_sqlalchemy.orm">module sqlalchemy.orm</a>.
</p>
<p>Options for the <code>mapper()</code> function:
</p>
<p><a href="sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_mapper">mapper()</a>.
</p>


    
    <A name="advdatamapping_mapper_columns"></a>
    
    <div class="sectionL3">

    <h3>Customizing Column Properties</h3>
    
    

<p>The default behavior of a <code>mapper</code> is to assemble all the columns in the mapped <code>Table</code> into mapped object attributes.  This behavior can be modified in several ways, as well as enhanced by SQL expressions.
</p>
<p>To load only a part of the columns referenced by a table as attributes, use the <code>include_properties</code> and <code>exclude_properties</code> arguments:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">include_properties</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_literal">'user_id'</span><span class="python_operator">, </span><span class="python_literal">'user_name'</span><span class="python_enclosure">])</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_operator">, </span><span class="python_name">exclude_properties</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_literal">'street'</span><span class="python_operator">, </span><span class="python_literal">'city'</span><span class="python_operator">, </span><span class="python_literal">'state'</span><span class="python_operator">, </span><span class="python_literal">'zip'</span><span class="python_enclosure">])</span><span class="python_operator">
</span></pre>
    </div>
<p>To change the name of the attribute mapped to a particular column, place the <code>Column</code> object in the <code>properties</code> dictionary with the desired key:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{ </span>
   <span class="python_literal">'id' </span><span class="python_operator">: </span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">,</span>
   <span class="python_literal">'name' </span><span class="python_operator">: </span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_name</span><span class="python_operator">,</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>To change the names of all attributes using a prefix, use the <code>column_prefix</code> option.  This is useful for classes which wish to add their own <code>property</code> accessors:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">column_prefix</span><span class="python_operator">=</span><span class="python_literal">'_'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The above will place attribute names such as <code>_user_id</code>, <code>_user_name</code>, <code>_password</code> etc. on the mapped <code>User</code> class.
</p>
<p>To place multiple columns which are known to be "synonymous" based on foreign key relationship or join condition into the same mapped attribute, put  them together using a list, as below where we map to a <code>Join</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># join users and addresses
</span><span class="python_name">usersaddresses </span><span class="python_operator">= </span><span class="python_name">sql</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_operator">, \</span>
    <span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id </span><span class="python_operator">== </span><span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">usersaddresses</span><span class="python_operator">, </span>
   <span class="python_name">properties </span><span class="python_operator">= </span><span class="python_enclosure">{</span>
           <span class="python_literal">'id'</span><span class="python_operator">:</span><span class="python_enclosure">[</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">]</span><span class="python_operator">,</span>
      <span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_deferred"></a>
    
    <div class="sectionL3">

    <h3>Deferred Column Loading</h3>
    
    

<p>This feature allows particular columns of a table to not be loaded by default, instead being loaded later on when first referenced.  It is essentially "column-level lazy loading".   This feature is useful when one wants to avoid loading a large text or binary field into memory when it's not needed.  Individual columns can be lazy loaded by themselves or placed into groups that lazy-load together.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">book_excerpts </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'books'</span><span class="python_operator">, </span><span class="python_name">db</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'book_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'title'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">200</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">nullable</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'summary'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">2000</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'excerpt'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'photo'</span><span class="python_operator">, </span><span class="python_name">Binary</span><span class="python_enclosure">)</span>
  <span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">Book</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_comment"># define a mapper that will load each of 'excerpt' and 'photo' in 
# separate, individual-row SELECT statements when each attribute
# is first referenced on the individual object instance
</span><span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Book</span><span class="python_operator">, </span><span class="python_name">book_excerpts</span><span class="python_operator">, </span><span class="python_name">properties </span><span class="python_operator">= </span><span class="python_enclosure">{</span>
  <span class="python_literal">'excerpt' </span><span class="python_operator">: </span><span class="python_name">deferred</span><span class="python_enclosure">(</span><span class="python_name">book_excerpts</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">excerpt</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_literal">'photo' </span><span class="python_operator">: </span><span class="python_name">deferred</span><span class="python_enclosure">(</span><span class="python_name">book_excerpts</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">photo</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>Deferred columns can be placed into groups so that they load together:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">book_excerpts </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'books'</span><span class="python_operator">, </span><span class="python_name">db</span><span class="python_operator">, </span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'book_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'title'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">200</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">nullable</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'summary'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">2000</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'excerpt'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'photo1'</span><span class="python_operator">, </span><span class="python_name">Binary</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'photo2'</span><span class="python_operator">, </span><span class="python_name">Binary</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'photo3'</span><span class="python_operator">, </span><span class="python_name">Binary</span><span class="python_enclosure">)</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">Book</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
  </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_comment"># define a mapper with a 'photos' deferred group.  when one photo is referenced,
# all three photos will be loaded in one SELECT statement.  The 'excerpt' will 
# be loaded separately when it is first referenced.
</span><span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Book</span><span class="python_operator">, </span><span class="python_name">book_excerpts</span><span class="python_operator">, </span><span class="python_name">properties </span><span class="python_operator">= </span><span class="python_enclosure">{</span>
  <span class="python_literal">'excerpt' </span><span class="python_operator">: </span><span class="python_name">deferred</span><span class="python_enclosure">(</span><span class="python_name">book_excerpts</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">excerpt</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_literal">'photo1' </span><span class="python_operator">: </span><span class="python_name">deferred</span><span class="python_enclosure">(</span><span class="python_name">book_excerpts</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">photo1</span><span class="python_operator">, </span><span class="python_name">group</span><span class="python_operator">=</span><span class="python_literal">'photos'</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_literal">'photo2' </span><span class="python_operator">: </span><span class="python_name">deferred</span><span class="python_enclosure">(</span><span class="python_name">book_excerpts</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">photo2</span><span class="python_operator">, </span><span class="python_name">group</span><span class="python_operator">=</span><span class="python_literal">'photos'</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_literal">'photo3' </span><span class="python_operator">: </span><span class="python_name">deferred</span><span class="python_enclosure">(</span><span class="python_name">book_excerpts</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">photo3</span><span class="python_operator">, </span><span class="python_name">group</span><span class="python_operator">=</span><span class="python_literal">'photos'</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>You can defer or undefer columns at the <code>Query</code> level using the <code>defer</code> and <code>undefer</code> options:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Book</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">defer</span><span class="python_enclosure">(</span><span class="python_literal">'summary'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">undefer</span><span class="python_enclosure">(</span><span class="python_literal">'excerpt'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>And an entire "deferred group", i.e. which uses the <code>group</code> keyword argument to <code>deferred()</code>, can be undeferred using <code>undefer_group()</code>, sending in the group name:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Book</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">undefer_group</span><span class="python_enclosure">(</span><span class="python_literal">'photos'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_expressions"></a>
    
    <div class="sectionL3">

    <h3>SQL Expressions as Mapped Attributes</h3>
    
    

<p>To add a SQL clause composed of local or external columns as a read-only, mapped column attribute, use the <code>column_property()</code> function.  Any scalar-returning <code>ClauseElement</code> may be used, as long as it has a <code>name</code> attribute; usually, you'll want to call <code>label()</code> to give it a specific name:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'fullname' </span><span class="python_operator">: </span><span class="python_name">column_property</span><span class="python_enclosure">(</span>
        <span class="python_enclosure">(</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">firstname </span><span class="python_operator">+ </span><span class="python_literal">" " </span><span class="python_operator">+ </span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">lastname</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">label</span><span class="python_enclosure">(</span><span class="python_literal">'fullname'</span><span class="python_enclosure">)</span>
    <span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>Correlated subqueries may be used as well:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'address_count' </span><span class="python_operator">: </span><span class="python_name">column_property</span><span class="python_enclosure">(</span>
            <span class="python_name">select</span><span class="python_enclosure">(</span>
                <span class="python_enclosure">[</span><span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">count</span><span class="python_enclosure">(</span><span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">address_id</span><span class="python_enclosure">)]</span><span class="python_operator">, </span>
                <span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span>
            <span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">label</span><span class="python_enclosure">(</span><span class="python_literal">'address_count'</span><span class="python_enclosure">)</span>
        <span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_overriding"></a>
    
    <div class="sectionL3">

    <h3>Overriding Attribute Behavior with Synonyms</h3>
    
    

<p>A common request is the ability to create custom class properties that override the behavior of setting/getting an attribute.  As of 0.4.2, the <code>synonym()</code> construct provides an easy way to do this in conjunction with a normal Python <code>property</code> constructs.  Below, we re-map the <code>email</code> column of our mapped table to a custom attribute setter/getter, mapping the actual column to the property named <code>_email</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">MyAddress</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
   </span><span class="python_keyword">def </span><span class="python_name">_set_email</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">email</span><span class="python_enclosure">)</span><span class="python_operator">:
      </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">_email </span><span class="python_operator">= </span><span class="python_name">email</span><span class="python_operator">
   </span><span class="python_keyword">def </span><span class="python_name">_get_email</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
      </span><span class="python_keyword">return </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">_email</span><span class="python_operator">
   </span><span class="python_name">email </span><span class="python_operator">= </span><span class="python_name">property</span><span class="python_enclosure">(</span><span class="python_name">_get_email</span><span class="python_operator">, </span><span class="python_name">_set_email</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">MyAddress</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_operator">, </span><span class="python_name">properties </span><span class="python_operator">= </span><span class="python_enclosure">{</span>
    <span class="python_literal">'email'</span><span class="python_operator">:</span><span class="python_name">synonym</span><span class="python_enclosure">(</span><span class="python_literal">'_email'</span><span class="python_operator">, </span><span class="python_name">map_column</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>The <code>email</code> attribute is now usable in the same way as any other mapped attribute, including filter expressions, get/set operations, etc.:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">address </span><span class="python_operator">= </span><span class="python_name">sess</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">MyAddress</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">MyAddress</span><span class="python_operator">.</span><span class="python_name">email </span><span class="python_operator">== </span><span class="python_literal">'some address'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">one</span><span class="python_enclosure">()</span><span class="python_operator">
</span>
<span class="python_name">address</span><span class="python_operator">.</span><span class="python_name">email </span><span class="python_operator">= </span><span class="python_literal">'some other address'</span><span class="python_operator">
</span><span class="python_name">sess</span><span class="python_operator">.</span><span class="python_name">flush</span><span class="python_enclosure">()</span><span class="python_operator">
</span>
<span class="python_name">q </span><span class="python_operator">= </span><span class="python_name">sess</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">MyAddress</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter_by</span><span class="python_enclosure">(</span><span class="python_name">email</span><span class="python_operator">=</span><span class="python_literal">'some other address'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>If the mapped class does not provide a property, the <code>synonym()</code> construct will create a default getter/setter object automatically.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_composite"></a>
    
    <div class="sectionL3">

    <h3>Composite Column Types</h3>
    
    

<p>Sets of columns can be associated with a single datatype.  The ORM treats the group of columns like a single column which accepts and returns objects using the custom datatype you provide.  In this example, we'll create a table <code>vertices</code> which stores a pair of x/y coordinates, and a custom datatype <code>Point</code> which is a composite type of an x and y column:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">vertices </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'vertices'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'x1'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'y1'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'x2'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'y2'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The requirements for the custom datatype class are that it have a constructor which accepts positional arguments corresponding to its column format, and also provides a method <code>__composite_values__()</code> which returns the state of the object as a list or tuple, in order of its column-based attributes.  It also should supply adequate <code>__eq__()</code> and <code>__ne__()</code> methods which test the equality of two instances:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">Point</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">def </span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">x</span><span class="python_operator">, </span><span class="python_name">y</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">x </span><span class="python_operator">= </span><span class="python_name">x</span><span class="python_operator">
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">y </span><span class="python_operator">= </span><span class="python_name">y</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">__composite_values__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_enclosure">[</span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">x</span><span class="python_operator">, </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">y</span><span class="python_enclosure">]            </span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">__eq__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">other</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_name">other</span><span class="python_operator">.</span><span class="python_name">x </span><span class="python_operator">== </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">x </span><span class="python_keyword">and </span><span class="python_name">other</span><span class="python_operator">.</span><span class="python_name">y </span><span class="python_operator">== </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">y</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">__ne__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">other</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return not </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">__eq__</span><span class="python_enclosure">(</span><span class="python_name">other</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Setting up the mapping uses the <code>composite()</code> function:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">Vertex</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Vertex</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'start'</span><span class="python_operator">:</span><span class="python_name">composite</span><span class="python_enclosure">(</span><span class="python_name">Point</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">x1</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">y1</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_literal">'end'</span><span class="python_operator">:</span><span class="python_name">composite</span><span class="python_enclosure">(</span><span class="python_name">Point</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">x2</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">y2</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>We can now use the <code>Vertex</code> instances as well as querying as though the <code>start</code> and <code>end</code> attributes are regular scalar attributes:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">sess </span><span class="python_operator">= </span><span class="python_name">Session</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">v </span><span class="python_operator">= </span><span class="python_name">Vertex</span><span class="python_enclosure">(</span><span class="python_name">Point</span><span class="python_enclosure">(</span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_number">4</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">Point</span><span class="python_enclosure">(</span><span class="python_number">5</span><span class="python_operator">, </span><span class="python_number">6</span><span class="python_enclosure">))</span><span class="python_operator">
</span><span class="python_name">sess</span><span class="python_operator">.</span><span class="python_name">save</span><span class="python_enclosure">(</span><span class="python_name">v</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">v2 </span><span class="python_operator">= </span><span class="python_name">sess</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Vertex</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Vertex</span><span class="python_operator">.</span><span class="python_name">start </span><span class="python_operator">== </span><span class="python_name">Point</span><span class="python_enclosure">(</span><span class="python_number">3</span><span class="python_operator">, </span><span class="python_number">4</span><span class="python_enclosure">))</span><span class="python_operator">
</span></pre>
    </div>
<p>The "equals" comparison operation by default produces an AND of all corresponding columns equated to one another.  If you'd like to override this, or define the behavior of other SQL operators for your new type, the <code>composite()</code> function accepts an extension object of type <code>sqlalchemy.orm.PropComparator</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">orm </span><span class="python_keyword">import </span><span class="python_name">PropComparator</span><span class="python_operator">
</span><span class="python_keyword">from </span><span class="python_name">sqlalchemy </span><span class="python_keyword">import </span><span class="python_name">sql</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">PointComparator</span><span class="python_enclosure">(</span><span class="python_name">PropComparator</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">def </span><span class="python_name">__gt__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">other</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_literal">"""define the 'greater than' operation"""</span><span class="python_operator">
</span>
        <span class="python_keyword">return </span><span class="python_name">sql</span><span class="python_operator">.</span><span class="python_name">and_</span><span class="python_enclosure">(</span><span class="python_operator">*</span><span class="python_enclosure">[</span><span class="python_name">a</span><span class="python_operator">&gt;</span><span class="python_name">b </span><span class="python_keyword">for </span><span class="python_name">a</span><span class="python_operator">, </span><span class="python_name">b </span><span class="python_keyword">in</span>
                          <span class="python_name">zip</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">prop</span><span class="python_operator">.</span><span class="python_name">columns</span><span class="python_operator">,</span>
                              <span class="python_name">other</span><span class="python_operator">.</span><span class="python_name">__composite_values__</span><span class="python_enclosure">())])</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">maper</span><span class="python_enclosure">(</span><span class="python_name">Vertex</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'start'</span><span class="python_operator">:</span><span class="python_name">composite</span><span class="python_enclosure">(</span><span class="python_name">Point</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">x1</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">y1</span><span class="python_operator">, </span><span class="python_name">comparator</span><span class="python_operator">=</span><span class="python_name">PointComparator</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_literal">'end'</span><span class="python_operator">:</span><span class="python_name">composite</span><span class="python_enclosure">(</span><span class="python_name">Point</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">x2</span><span class="python_operator">, </span><span class="python_name">vertices</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">y2</span><span class="python_operator">, </span><span class="python_name">comparator</span><span class="python_operator">=</span><span class="python_name">PointComparator</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_orderby"></a>
    
    <div class="sectionL3">

    <h3>Controlling Ordering</h3>
    
    

<p>By default, mappers will attempt to ORDER BY the "oid" column of a table, or the first primary key column, when selecting rows.  This can be modified in several ways.
</p>
<p>The "order_by" parameter can be sent to a mapper, overriding the per-engine ordering if any.  A value of None means that the mapper should not use any ordering.  A non-None value, which can be a column, an <code>asc</code> or <code>desc</code> clause, or an array of either one, indicates the ORDER BY clause that should be added to all select queries:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># disable all ordering
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">order_by</span><span class="python_operator">=</span><span class="python_name">None</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># order by a column
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">order_by</span><span class="python_operator">=</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># order by multiple items
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">order_by</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_name</span><span class="python_operator">.</span><span class="python_name">desc</span><span class="python_enclosure">()])</span><span class="python_operator">
</span></pre>
    </div>
<p>"order_by" can also be specified with queries, overriding all other per-engine/per-mapper orderings:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># order by a column
</span><span class="python_name">l </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">.</span><span class="python_name">user_name</span><span class="python_operator">==</span><span class="python_literal">'fred'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">order_by</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span>
<span class="python_comment"># order by multiple criterion
</span><span class="python_name">l </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">.</span><span class="python_name">user_name</span><span class="python_operator">==</span><span class="python_literal">'fred'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">order_by</span><span class="python_enclosure">([</span><span class="python_name">User</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span><span class="python_name">User</span><span class="python_operator">.</span><span class="python_name">user_name</span><span class="python_operator">.</span><span class="python_name">desc</span><span class="python_enclosure">()])</span><span class="python_operator">
</span></pre>
    </div>
<p>The "order_by" property can also be specified on a <code>relation()</code> which will control the ordering of the collection:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># order address objects by address id
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties </span><span class="python_operator">= </span><span class="python_enclosure">{</span>
    <span class="python_literal">'addresses' </span><span class="python_operator">: </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">order_by</span><span class="python_operator">=</span><span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">address_id</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>Note that when using eager loaders with relations, the tables used by the eager load's join are anonymously aliased.  You can only order by these columns if you specify it at the <code>relation()</code> level.  To control ordering at the query level based on a related table, you <code>join()</code> to that relation, then order by it:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_literal">'addresses'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">order_by</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">.</span><span class="python_name">street</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_inheritance"></a>
    
    <div class="sectionL3">

    <h3>Mapping Class Inheritance Hierarchies</h3>
    
    

<p>SQLAlchemy supports three forms of inheritance:  <em>single table inheritance</em>, where several types of classes are stored in one table, <em>concrete table inheritance</em>, where each type of class is stored in its own table, and <em>joined table inheritance</em>, where the parent/child classes are stored in their own tables that are joined together in a select.  Whereas support for single and joined table inheritance is strong, concrete table inheritance is a less common scenario with some particular problems so is not quite as flexible.
</p>
<p>When mappers are configured in an inheritance relationship, SQLAlchemy has the ability to load elements "polymorphically", meaning that a single query can return objects of multiple types.
</p>
<p>For the following sections, assume this class relationship:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">Employee</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">def </span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">name</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">= </span><span class="python_name">name</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">__repr__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">__class__</span><span class="python_operator">.</span><span class="python_name">__name__ </span><span class="python_operator">+ </span><span class="python_literal">" " </span><span class="python_operator">+ </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_keyword">class </span><span class="python_name">Manager</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">def </span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">name</span><span class="python_operator">, </span><span class="python_name">manager_data</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">= </span><span class="python_name">name</span><span class="python_operator">
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">manager_data </span><span class="python_operator">= </span><span class="python_name">manager_data</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">__repr__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">__class__</span><span class="python_operator">.</span><span class="python_name">__name__ </span><span class="python_operator">+ </span><span class="python_literal">" " </span><span class="python_operator">+ </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">+ </span><span class="python_literal">" " </span><span class="python_operator">+  </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">manager_data</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_keyword">class </span><span class="python_name">Engineer</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">def </span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">name</span><span class="python_operator">, </span><span class="python_name">engineer_info</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">= </span><span class="python_name">name</span><span class="python_operator">
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">engineer_info </span><span class="python_operator">= </span><span class="python_name">engineer_info</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">__repr__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">__class__</span><span class="python_operator">.</span><span class="python_name">__name__ </span><span class="python_operator">+ </span><span class="python_literal">" " </span><span class="python_operator">+ </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">name </span><span class="python_operator">+ </span><span class="python_literal">" " </span><span class="python_operator">+  </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">engineer_info</span><span class="python_operator">
</span></pre>
    </div>

    
    <A name="advdatamapping_mapper_inheritance_joined"></a>
    
    <div class="sectionL4">

    <h3>Joined Table Inheritance</h3>
    
    
<p>In joined table inheritance, each class along a particular classes' list of parents is represented by a unique table.  The total set of attributes for a particular instance is represented as a join along all tables in its inheritance path.  Here, we first define a table to represent the <code>Employee</code> class.  This table will contain a primary key column (or columns), and a column for each attribute that's represented by <code>Employee</code>.  In this case it's just <code>name</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">employees </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'employees'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'type'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">30</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">nullable</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">)</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The table also has a column called <code>type</code>.  It is strongly advised in both single- and joined- table inheritance scenarios that the root table contains a column whose sole purpose is that of the <strong>discriminator</strong>; it stores a value which indicates the type of object represented within the row.  The column may be of any desired datatype.  While there are some "tricks" to work around the requirement that there be a discriminator column, they are more complicated to configure when one wishes to load polymorphically.
</p>
<p>Next we define individual tables for each of <code>Engineer</code> and <code>Manager</code>, which each contain columns that represent the attributes unique to the subclass they represent.  Each table also must contain a primary key column (or columns), and in most cases a foreign key reference to the parent table.  It is  standard practice that the same column is used for both of these roles, and that the column is also named the same as that of the parent table.  However this is optional in SQLAlchemy; separate columns may be used for primary key and parent-relation, the column may be named differently than that of the parent, and even a custom join condition can be specified between parent and child tables instead of using a foreign key.  In joined table inheritance, the primary key of an instance is always represented by the primary key of the base table only (new in SQLAlchemy 0.4).
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">engineers </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'engineers'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'employees.employee_id'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'engineer_info'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">managers </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'managers'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'employees.employee_id'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'manager_data'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>We then configure mappers as usual, except we use some additional arguments to indicate the inheritance relationship, the polymorphic discriminator column, and the <strong>polymorphic identity</strong> of each class; this is the value that will be stored in the polymorphic discriminator column.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">employees</span><span class="python_operator">, </span><span class="python_name">polymorphic_on</span><span class="python_operator">=</span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">type</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'employee'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">, </span><span class="python_name">engineers</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'engineer'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Manager</span><span class="python_operator">, </span><span class="python_name">managers</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'manager'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>And that's it.  Querying against <code>Employee</code> will return a combination of <code>Employee</code>, <code>Engineer</code> and <code>Manager</code> objects.
</p>

    
    <A name="advdatamapping_mapper_inheritance_joined_querying"></a>
    
    <div class="sectionL4">

    <h3>Polymorphic Querying Strategies</h3>
    
    
<p>The <code>Query</code> object includes some helper functionality when dealing with joined-table inheritance mappings.  These are the <code>with_polymorphic()</code> and <code>of_type()</code> methods, both of which are introduced in version 0.4.4.
</p>
<p>The <code>with_polymorphic()</code> method affects the specific subclass tables which the Query selects from.  Normally, a query such as this:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_operator">==</span><span class="python_literal">'ed'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Selects only from the <code>employees</code> table.  The criterion we use in <code>filter()</code> and other methods will generate WHERE criterion against this table.  What if we wanted to load <code>Employee</code> objects but also wanted to use criterion against <code>Engineer</code> ?  We could just query against the <code>Engineer</code> class instead.  But, if we were using criterion which filters among more than one subclass (subclasses which do not inherit directly from one to the other), we'd like to select from an outer join of all those tables.  The <code>with_polymorphic()</code> method can tell <code>Query</code> which joined-table subclasses we want to select for:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">with_polymorphic</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">.</span><span class="python_name">engineer_info</span><span class="python_operator">==</span><span class="python_literal">'some info'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Even without criterion, the <code>with_polymorphic()</code> method has the added advantage that instances are loaded from all of their tables in one result set.  Such as, to optimize the loading of all <code>Employee</code> objects, <code>with_polymorphic()</code> accepts <code>'*'</code> as a wildcard indicating that all subclass tables should be joined:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">with_polymorphic</span><span class="python_enclosure">(</span><span class="python_literal">'*'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p><code>with_polymorphic()</code> is an effective query-level alternative to the existing <code>select_table</code> option available on <code>mapper()</code>.
</p>
<p>Next is a way to join along <code>relation</code> paths while narrowing the criterion to specific subclasses.  Suppose the <code>employees</code> table represents a collection of employees which are associated with a <code>Company</code> object.  We'll add a <code>company_id</code> column to the <code>employees</code> table and a new table <code>companies</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">companies </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'companies'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'company_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span>
   <span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">employees </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'employees'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'type'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">30</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">nullable</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
  <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'company_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'companies.company_id'</span><span class="python_enclosure">))</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">Company</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_operator">, </span><span class="python_name">companies</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'employees'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>If we wanted to join from <code>Company</code> to not just <code>Employee</code> but specifically <code>Engineers</code>, using the <code>join()</code> method or <code>any()</code> or <code>has()</code> operators will by default create a join from <code>companies</code> to <code>employees</code>, without including <code>engineers</code> or <code>managers</code> in the mix.  If we wish to have criterion which is specifically against the <code>Engineer</code> class, we can tell those methods to join or subquery against the full set of tables representing the subclass using the <code>of_type()</code> opertator:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_operator">.</span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">of_type</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">.</span><span class="python_name">engineer_info</span><span class="python_operator">==</span><span class="python_literal">'someinfo'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>A longhand notation, introduced in 0.4.3, is also available, which involves spelling out the full target selectable within a 2-tuple:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">((</span><span class="python_literal">'employees'</span><span class="python_operator">, </span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">engineers</span><span class="python_enclosure">)))</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">.</span><span class="python_name">engineer_info</span><span class="python_operator">==</span><span class="python_literal">'someinfo'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The second notation allows more flexibility, such as joining to any group of subclass tables:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">((</span><span class="python_literal">'employees'</span><span class="python_operator">, </span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">engineers</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">managers</span><span class="python_enclosure">)))</span><span class="python_operator">.\
    </span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">or_</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">.</span><span class="python_name">engineer_info</span><span class="python_operator">==</span><span class="python_literal">'someinfo'</span><span class="python_operator">, </span><span class="python_name">Manager</span><span class="python_operator">.</span><span class="python_name">manager_data</span><span class="python_operator">==</span><span class="python_literal">'somedata'</span><span class="python_enclosure">))</span><span class="python_operator">
</span></pre>
    </div>
<p>The <code>any()</code> and <code>has()</code> operators also can be used with <code>of_type()</code> when the embedded criterion is in terms of a subclass:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_operator">.</span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">of_type</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">any</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">.</span><span class="python_name">engineer_info</span><span class="python_operator">==</span><span class="python_literal">'someinfo'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>Note that the <code>any()</code> and <code>has()</code> are both shorthand for a correlated EXISTS query.  To build one by hand looks like:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span>
    <span class="python_name">exists</span><span class="python_enclosure">([</span><span class="python_number">1</span><span class="python_enclosure">]</span><span class="python_operator">, </span>
        <span class="python_name">and_</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">.</span><span class="python_name">engineer_info</span><span class="python_operator">==</span><span class="python_literal">'someinfo'</span><span class="python_operator">, </span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">company_id</span><span class="python_operator">==</span><span class="python_name">companies</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">company_id</span><span class="python_enclosure">)</span><span class="python_operator">, </span>
        <span class="python_name">from_obj</span><span class="python_operator">=</span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">engineers</span><span class="python_enclosure">)</span>
    <span class="python_enclosure">)</span>
<span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>The EXISTS subquery above selects from the join of <code>employees</code> to <code>engineers</code>, and also specifies criterion which correlates the EXISTS subselect back to the parent <code>companies</code> table.
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_inheritance_joined_optimizing"></a>
    
    <div class="sectionL4">

    <h3>Optimizing Joined Table Loads</h3>
    
    
<p>When loading fresh from the database, the joined-table setup above will query from the parent table first, then for each row will issue a second query to the child table.  For example, for a load of five rows with <code>Employee</code> id 3, <code>Manager</code> ids 1 and 5 and <code>Engineer</code> ids 2 and 4, will produce queries along the lines of this example:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre><div class="codepop">SELECT employees.employee_id AS employees_employee_id, employees.name AS employees_name, employees.type AS employees_type <br/>
FROM employees ORDER BY employees.oid<br/>
[]<br/>
SELECT managers.employee_id AS managers_employee_id, managers.manager_data AS managers_manager_data <br/>
FROM managers <br/>
WHERE ? = managers.employee_id<br/>
[5]<br/>
SELECT engineers.employee_id AS engineers_employee_id, engineers.engineer_info AS engineers_engineer_info <br/>
FROM engineers <br/>
WHERE ? = engineers.employee_id<br/>
[2]<br/>
SELECT engineers.employee_id AS engineers_employee_id, engineers.engineer_info AS engineers_engineer_info <br/>
FROM engineers <br/>
WHERE ? = engineers.employee_id<br/>
[4]<br/>
SELECT managers.employee_id AS managers_employee_id, managers.manager_data AS managers_manager_data <br/>
FROM managers <br/>
WHERE ? = managers.employee_id<br/>
[1]</div><pre>

<span class="python_operator"></span></pre>
    </div>
<p>The above query works well for a <code>get()</code> operation, since it limits the queries to only the tables directly involved in fetching a single instance.  For instances which are already present in the session, the secondary table load is not needed.  However, the above loading style is not efficient for loading large groups of objects, as it incurs separate queries for each parent row.
</p>
<p>One way to reduce the number of "secondary" loads of child rows is to "defer" them, using <code>polymorphic_fetch='deferred'</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">employees</span><span class="python_operator">, </span><span class="python_name">polymorphic_on</span><span class="python_operator">=</span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">type</span><span class="python_operator">, \</span>
    <span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'employee'</span><span class="python_operator">, </span><span class="python_name">polymorphic_fetch</span><span class="python_operator">=</span><span class="python_literal">'deferred'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">, </span><span class="python_name">engineers</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'engineer'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Manager</span><span class="python_operator">, </span><span class="python_name">managers</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'manager'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The above configuration queries in the same manner as earlier, except the load of each "secondary" table occurs only when attributes referencing those columns are first referenced on the loaded instance.  This style of loading is very efficient for cases where large selects of items occur, but a detailed "drill down" of extra inherited properties is less common.
</p>
<p>More commonly, an all-at-once load may be achieved by constructing a query which combines all three tables together.  The easiest way to do this as of version 0.4.4 is to use the <code>with_polymorphic()</code> query method which will automatically join in the classes desired:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">with_polymorphic</span><span class="python_enclosure">([</span><span class="python_name">Engineer</span><span class="python_operator">, </span><span class="python_name">Manager</span><span class="python_enclosure">])</span><span class="python_operator">
</span></pre>
    </div>
<p>Which produces a query like the following:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre><div class="codepop">SELECT employees.employee_id AS employees_employee_id, engineers.employee_id AS engineers_employee_id, managers.employee_id AS managers_employee_id, employees.name AS employees_name, employees.type AS employees_type, engineers.engineer_info AS engineers_engineer_info, managers.manager_data AS managers_manager_data <br/>
FROM employees LEFT OUTER JOIN engineers ON employees.employee_id = engineers.employee_id LEFT OUTER JOIN managers ON employees.employee_id = managers.employee_id ORDER BY employees.oid<br/>
[]</div><pre>

<span class="python_operator"></span></pre>
    </div>
<p><code>with_polymorphic()</code> accepts a single class or mapper, a list of classes/mappers, or the string <code>'*'</code> to indicate all subclasses.  It also accepts a second argument <code>selectable</code> which replaces the automatic join creation and instead selects directly from the selectable given.  This can allow polymorphic loads from a variety of inheritance schemes including concrete tables, if the appropriate unions are constructed.
</p>
<p>Similar behavior as provided by <code>with_polymorphic()</code> can be configured at the mapper level so that any user-defined query is used by default in order to load instances.  The <code>select_table</code> argument references an arbitrary selectable which the mapper will use for load operations (it has no impact on save operations).  Any selectable can be used for this, such as a UNION of tables.  For joined table inheritance, the easiest method is to use OUTER JOIN:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">join </span><span class="python_operator">= </span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">engineers</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">managers</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">employees</span><span class="python_operator">, </span><span class="python_name">polymorphic_on</span><span class="python_operator">=</span><span class="python_name">employees</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">type</span><span class="python_operator">, \</span>
    <span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'employee'</span><span class="python_operator">, </span><span class="python_name">select_table</span><span class="python_operator">=</span><span class="python_name">join</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">, </span><span class="python_name">engineers</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'engineer'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Manager</span><span class="python_operator">, </span><span class="python_name">managers</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'manager'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The above mapping will produce a query similar to that of <code>with_polymorphic('*')</code> for every query of <code>Employee</code> objects.
</p>
<p>When <code>select_table</code> is used, <code>with_polymorphic()</code> still overrides its usage at the query level.  For example, if <code>select_table</code> were configured to load from a join of multiple tables, using <code>with_polymorphic(Employee)</code> will limit the list of tables selected from to just the base table (as always, tables which don't get loaded in the first pass will be loaded on an as-needed basis).
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="advdatamapping_mapper_inheritance_single"></a>
    
    <div class="sectionL4">

    <h3>Single Table Inheritance</h3>
    
    
<p>Single table inheritance is where the attributes of the base class as well as all subclasses are represented within a single table.  A column is present in the table for every attribute mapped to the base class and all subclasses; the columns which correspond to a single subclass are nullable.  This configuration looks much like joined-table inheritance except there's only one table.  In this case, a <code>type</code> column is required, as there would be no other way to discriminate between classes.  The table is specified in the base mapper only; for the inheriting classes, leave their <code>table</code> parameter blank:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">employees_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'employees'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'manager_data'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'engineer_info'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'type'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">20</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">nullable</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">)</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">employee_mapper </span><span class="python_operator">= </span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">employees_table</span><span class="python_operator">, \</span>
    <span class="python_name">polymorphic_on</span><span class="python_operator">=</span><span class="python_name">employees_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">type</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'employee'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">manager_mapper </span><span class="python_operator">= </span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Manager</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">employee_mapper</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'manager'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">engineer_mapper </span><span class="python_operator">= </span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">employee_mapper</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'engineer'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Note that the mappers for the derived classes Manager and Engineer omit the specification of their associated table, as it is inherited from the employee_mapper. Omitting the table specification for derived mappers in single-table inheritance is required.
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_inheritance_concrete"></a>
    
    <div class="sectionL4">

    <h3>Concrete Table Inheritance</h3>
    
    
<p>This form of inheritance maps each class to a distinct table, as below:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">employees_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'employees'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">managers_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'managers'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'manager_data'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">engineers_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'engineers'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'engineer_info'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Notice in this case there is no <code>type</code> column.  If polymorphic loading is not required, there's no advantage to using <code>inherits</code> here; you just define a separate mapper for each class.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">employees_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Manager</span><span class="python_operator">, </span><span class="python_name">managers_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">, </span><span class="python_name">engineers_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>To load polymorphically, the <code>select_table</code> argument is currently required.  In this case we must construct a UNION of all three tables.  SQLAlchemy includes a helper function to create these called <code>polymorphic_union</code>, which will map all the different columns into a structure of selects with the same numbers and names of columns, and also generate a virtual <code>type</code> column for each subselect:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">pjoin </span><span class="python_operator">= </span><span class="python_name">polymorphic_union</span><span class="python_enclosure">({</span>
    <span class="python_literal">'employee'</span><span class="python_operator">:</span><span class="python_name">employees_table</span><span class="python_operator">,</span>
    <span class="python_literal">'manager'</span><span class="python_operator">:</span><span class="python_name">managers_table</span><span class="python_operator">,</span>
    <span class="python_literal">'engineer'</span><span class="python_operator">:</span><span class="python_name">engineers_table</span>
<span class="python_enclosure">}</span><span class="python_operator">, </span><span class="python_literal">'type'</span><span class="python_operator">, </span><span class="python_literal">'pjoin'</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">employee_mapper </span><span class="python_operator">= </span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">employees_table</span><span class="python_operator">, </span><span class="python_name">select_table</span><span class="python_operator">=</span><span class="python_name">pjoin</span><span class="python_operator">, \</span>
    <span class="python_name">polymorphic_on</span><span class="python_operator">=</span><span class="python_name">pjoin</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">type</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'employee'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">manager_mapper </span><span class="python_operator">= </span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Manager</span><span class="python_operator">, </span><span class="python_name">managers_table</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">employee_mapper</span><span class="python_operator">, \</span>
    <span class="python_name">concrete</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'manager'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">engineer_mapper </span><span class="python_operator">= </span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">, </span><span class="python_name">engineers_table</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">employee_mapper</span><span class="python_operator">, \</span>
    <span class="python_name">concrete</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'engineer'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Upon select, the polymorphic union produces a query like this:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre><div class="codepop">SELECT pjoin.type AS pjoin_type, pjoin.manager_data AS pjoin_manager_data, pjoin.employee_id AS pjoin_employee_id, <br/>
pjoin.name AS pjoin_name, pjoin.engineer_info AS pjoin_engineer_info <br/>
FROM (<br/>
    SELECT employees.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name AS name, <br/>
    CAST(NULL AS VARCHAR(50)) AS engineer_info, 'employee' AS type <br/>
    FROM employees <br/>
UNION ALL <br/>
    SELECT managers.employee_id AS employee_id, managers.manager_data AS manager_data, managers.name AS name, <br/>
    CAST(NULL AS VARCHAR(50)) AS engineer_info, 'manager' AS type <br/>
    FROM managers <br/>
UNION ALL <br/>
    SELECT engineers.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name AS name, <br/>
    engineers.engineer_info AS engineer_info, 'engineer' AS type <br/>
    FROM engineers<br/>
) AS pjoin ORDER BY pjoin.oid<br/>
[]</div><pre>

<span class="python_operator"></span></pre>
    </div>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_inheritance_relations"></a>
    
    <div class="sectionL4">

    <h3>Using Relations with Inheritance</h3>
    
    
<p>Both joined-table and single table inheritance scenarios produce mappings which are usable in relation() functions; that is, it's possible to map a parent object to a child object which is polymorphic.  Similarly, inheriting mappers can have <code>relation()</code>s of their own at any level, which are inherited to each child class.  The only requirement for relations is that there is a table relationship between parent and child.  An example is the following modification to the joined table inheritance example, which sets a bi-directional relationship between <code>Employee</code> and <code>Company</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">employees_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'employees'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'company_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'companies.company_id'</span><span class="python_enclosure">))</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">companies </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'companies'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'company_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">)))</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">Company</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_operator">, </span><span class="python_name">companies</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
   <span class="python_literal">'employees'</span><span class="python_operator">: </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">backref</span><span class="python_operator">=</span><span class="python_literal">'company'</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>SQLAlchemy has a lot of experience in this area; the optimized "outer join" approach can be used freely for parent and child relationships, eager loads are fully useable, query aliasing and other tricks are fully supported as well.
</p>
<p>In a concrete inheritance scenario, mapping <code>relation()</code>s is more difficult since the distinct classes do not share a table.  In this case, you <em>can</em> establish a relationship from parent to child if a join condition can be constructed from parent to child, if each child table contains a foreign key to the parent:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">companies </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'companies'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
   <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">)))</span><span class="python_operator">
</span>
<span class="python_name">employees_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'employees'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'company_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'companies.id'</span><span class="python_enclosure">))</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">managers_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'managers'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'manager_data'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'company_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'companies.id'</span><span class="python_enclosure">))</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">engineers_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'engineers'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">, </span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'employee_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'name'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'engineer_info'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'company_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'companies.id'</span><span class="python_enclosure">))</span>
<span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_operator">, </span><span class="python_name">employees_table</span><span class="python_operator">, </span><span class="python_name">select_table</span><span class="python_operator">=</span><span class="python_name">pjoin</span><span class="python_operator">, </span><span class="python_name">polymorphic_on</span><span class="python_operator">=</span><span class="python_name">pjoin</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">type</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'employee'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Manager</span><span class="python_operator">, </span><span class="python_name">managers_table</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">employee_mapper</span><span class="python_operator">, </span><span class="python_name">concrete</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'manager'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Engineer</span><span class="python_operator">, </span><span class="python_name">engineers_table</span><span class="python_operator">, </span><span class="python_name">inherits</span><span class="python_operator">=</span><span class="python_name">employee_mapper</span><span class="python_operator">, </span><span class="python_name">concrete</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_operator">, </span><span class="python_name">polymorphic_identity</span><span class="python_operator">=</span><span class="python_literal">'engineer'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_operator">, </span><span class="python_name">companies</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'employees'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Employee</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>Let's crank it up and try loading with an eager load:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Company</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">eagerload</span><span class="python_enclosure">(</span><span class="python_literal">'employees'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre><div class="codepop">SELECT anon_1.type AS anon_1_type, anon_1.manager_data AS anon_1_manager_data, anon_1.engineer_info AS anon_1_engineer_info, <br/>
anon_1.employee_id AS anon_1_employee_id, anon_1.name AS anon_1_name, anon_1.company_id AS anon_1_company_id, <br/>
companies.id AS companies_id, companies.name AS companies_name <br/>
FROM companies LEFT OUTER JOIN (SELECT CAST(NULL AS VARCHAR(50)) AS engineer_info, employees.employee_id AS employee_id, <br/>
CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name AS name, employees.company_id AS company_id, 'employee' AS type <br/>
FROM employees UNION ALL SELECT CAST(NULL AS VARCHAR(50)) AS engineer_info, managers.employee_id AS employee_id, <br/>
managers.manager_data AS manager_data, managers.name AS name, managers.company_id AS company_id, 'manager' AS type <br/>
FROM managers UNION ALL SELECT engineers.engineer_info AS engineer_info, engineers.employee_id AS employee_id, <br/>
CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name AS name, engineers.company_id AS company_id, 'engineer' AS type <br/>
FROM engineers) AS anon_1 ON companies.id = anon_1.company_id ORDER BY companies.oid, anon_1.oid<br/>
[]</div><pre>

<span class="python_operator"></span></pre>
    </div>
<p>The big limitation with concrete table inheritance is that relation()s placed on each concrete mapper do <strong>not</strong> propagate to child mappers.  If you want to have the same relation()s set up on all concrete mappers, they must be configured manually on each.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="advdatamapping_mapper_joins"></a>
    
    <div class="sectionL3">

    <h3>Mapping a Class against Multiple Tables</h3>
    
    

<p>Mappers can be constructed against arbitrary relational units (called <code>Selectables</code>) as well as plain <code>Tables</code>.  For example, The <code>join</code> keyword from the SQL package creates a neat selectable unit comprised of multiple tables, complete with its own composite primary key, which can be passed in to a mapper as the table.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># a class
</span><span class="python_keyword">class </span><span class="python_name">AddressUser</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_comment"># define a Join
</span><span class="python_operator"></span><span class="python_name">j </span><span class="python_operator">= </span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># map to it - the identity of an AddressUser object will be 
# based on (user_id, address_id) since those are the primary keys involved
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">AddressUser</span><span class="python_operator">, </span><span class="python_name">j</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'user_id'</span><span class="python_operator">:</span><span class="python_enclosure">[</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">]</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>A second example:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># many-to-many join on an association table
</span><span class="python_name">j </span><span class="python_operator">= </span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">userkeywords</span><span class="python_operator">, </span>
        <span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_name">userkeywords</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">keywords</span><span class="python_operator">, </span>
           <span class="python_name">userkeywords</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">keyword_id</span><span class="python_operator">==</span><span class="python_name">keywords</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">keyword_id</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># a class 
</span><span class="python_keyword">class </span><span class="python_name">KeywordUser</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_comment"># map to it - the identity of a KeywordUser object will be
# (user_id, keyword_id) since those are the primary keys involved
</span><span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">KeywordUser</span><span class="python_operator">, </span><span class="python_name">j</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'user_id'</span><span class="python_operator">:</span><span class="python_enclosure">[</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span><span class="python_name">userkeywords</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">]</span><span class="python_operator">,</span>
    <span class="python_literal">'keyword_id'</span><span class="python_operator">:</span><span class="python_enclosure">[</span><span class="python_name">userkeywords</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">keyword_id</span><span class="python_operator">, </span><span class="python_name">keywords</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">keyword_id</span><span class="python_enclosure">]</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>In both examples above, "composite" columns were added as properties to the mappers; these are aggregations of multiple columns into one mapper property, which instructs the mapper to keep both of those columns set at the same value.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_selects"></a>
    
    <div class="sectionL3">

    <h3>Mapping a Class against Arbitrary Selects</h3>
    
    

<p>Similar to mapping against a join, a plain select() object can be used with a mapper as well.  Below, an example select which contains two aggregate functions and a group_by is mapped to a class:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">s </span><span class="python_operator">= </span><span class="python_name">select</span><span class="python_enclosure">([</span><span class="python_name">customers</span><span class="python_operator">, </span>
            <span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">count</span><span class="python_enclosure">(</span><span class="python_name">orders</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">label</span><span class="python_enclosure">(</span><span class="python_literal">'order_count'</span><span class="python_enclosure">)</span><span class="python_operator">, </span>
            <span class="python_name">func</span><span class="python_operator">.</span><span class="python_name">max</span><span class="python_enclosure">(</span><span class="python_name">orders</span><span class="python_operator">.</span><span class="python_name">price</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">label</span><span class="python_enclosure">(</span><span class="python_literal">'highest_order'</span><span class="python_enclosure">)]</span><span class="python_operator">,</span>
            <span class="python_name">customers</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">customer_id</span><span class="python_operator">==</span><span class="python_name">orders</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">customer_id</span><span class="python_operator">,</span>
            <span class="python_name">group_by</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_name">c </span><span class="python_keyword">for </span><span class="python_name">c </span><span class="python_keyword">in </span><span class="python_name">customers</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_enclosure">]</span>
            <span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">(</span><span class="python_literal">'somealias'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_keyword">class </span><span class="python_name">Customer</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Customer</span><span class="python_operator">, </span><span class="python_name">s</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Above, the "customers" table is joined against the "orders" table to produce a full row for each customer row, the total count of related rows in the "orders" table, and the highest price in the "orders" table, grouped against the full set of columns in the "customers" table.  That query is then mapped against the Customer class.  New instances of Customer will contain attributes for each column in the "customers" table as well as an "order_count" and "highest_order" attribute.  Updates to the Customer object will only be reflected in the "customers" table and not the "orders" table.  This is because the primary key columns of the "orders" table are not represented in this mapper and therefore the table is not affected by save or delete operations.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_multiple"></a>
    
    <div class="sectionL3">

    <h3>Multiple Mappers for One Class</h3>
    
    

<p>The first mapper created for a certain class is known as that class's "primary mapper."  Other mappers can be created as well, these come in two varieties.
</p>
<ul>
 <li><p><strong>secondary mapper</strong>
   this is a mapper that must be constructed with the keyword argument <code>non_primary=True</code>, and represents a load-only mapper.  Objects that are loaded with a secondary mapper will have their save operation processed by the primary mapper.  It is also invalid to add new <code>relation()</code>s to a non-primary mapper. To use this mapper with the Session, specify it to the <code>query</code> method:
</p>
<p>example:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># primary mapper
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># make a secondary mapper to load User against a join
</span><span class="python_name">othermapper </span><span class="python_operator">= </span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_name">someothertable</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">non_primary</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># select
</span><span class="python_name">result </span><span class="python_operator">= </span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">othermapper</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>The "non primary mapper" is a rarely needed feature of SQLAlchemy; in most cases, the <code>Query</code> object can produce any kind of query that's desired.  It's recommended that a straight <code>Query</code> be used in place of a non-primary mapper unless the mapper approach is absolutely needed.  Current use cases for the "non primary mapper" are when you want to map the class to a particular select statement or view to which additional query criterion can be added, and for when the particular mapped select statement or view is to be placed in a <code>relation()</code> of a parent mapper.
</p>

 </li>

 <li><p><strong>entity name mapper</strong>
   this is a mapper that is a fully functioning primary mapper for a class, which is distinguished from the regular primary mapper by an <code>entity_name</code> parameter.  Instances loaded with this mapper will be totally managed by this new mapper and have no connection to the original one.  Most methods on <code>Session</code> include an optional <code>entity_name</code> parameter in order to specify this condition.
</p>
<p>example:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># primary mapper
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># make an entity name mapper that stores User objects in another table
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">alternate_users_table</span><span class="python_operator">, </span><span class="python_name">entity_name</span><span class="python_operator">=</span><span class="python_literal">'alt'</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># make two User objects
</span><span class="python_name">user1 </span><span class="python_operator">= </span><span class="python_name">User</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">user2 </span><span class="python_operator">= </span><span class="python_name">User</span><span class="python_enclosure">()</span><span class="python_operator">
</span>
<span class="python_comment"># save one in in the "users" table
</span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">save</span><span class="python_enclosure">(</span><span class="python_name">user1</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># save the other in the "alternate_users_table"
</span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">save</span><span class="python_enclosure">(</span><span class="python_name">user2</span><span class="python_operator">, </span><span class="python_name">entity_name</span><span class="python_operator">=</span><span class="python_literal">'alt'</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">flush</span><span class="python_enclosure">()</span><span class="python_operator">
</span>
<span class="python_comment"># select from the alternate mapper
</span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">entity_name</span><span class="python_operator">=</span><span class="python_literal">'alt'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>Use the "entity name" mapper when different instances of the same class are persisted in completely different tables.  The "entity name" approach can also perform limited levels of horizontal partitioning as well.   A more comprehensive approach to horizontal partitioning is provided by the Sharding API.
</p>

 </li>
</ul>



            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_mapper_extending"></a>
    
    <div class="sectionL3">

    <h3>Extending Mapper</h3>
    
    

<p>Mappers can have functionality augmented or replaced at many points in its execution via the usage of the MapperExtension class.  This class is just a series of "hooks" where various functionality takes place.  An application can make its own MapperExtension objects, overriding only the methods it needs.  Methods that are not overridden return the special value <code>sqlalchemy.orm.EXT_CONTINUE</code> to allow processing to continue to the next MapperExtension or simply proceed normally if there are no more extensions.
</p>
<p>API documentation for MapperExtension: <a href="sqlalchemy_orm.html#docstrings_sqlalchemy.orm_MapperExtension">class MapperExtension(object)</a>
</p>
<p>To use MapperExtension, make your own subclass of it and just send it off to a mapper:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">m </span><span class="python_operator">= </span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">extension</span><span class="python_operator">=</span><span class="python_name">MyExtension</span><span class="python_enclosure">())</span><span class="python_operator">
</span></pre>
    </div>
<p>Multiple extensions will be chained together and processed in order; they are specified as a list:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">m </span><span class="python_operator">= </span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">extension</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_name">ext1</span><span class="python_operator">, </span><span class="python_name">ext2</span><span class="python_operator">, </span><span class="python_name">ext3</span><span class="python_enclosure">])</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="advdatamapping_relation"></a>
    
    <div class="sectionL2">

    <h3>Relation Configuration</h3>
    
    

<p>The full list of options for the <code>relation()</code> function:
</p>
<p><a href="sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_relation">relation()</a>
</p>


    
    <A name="advdatamapping_relation_patterns"></a>
    
    <div class="sectionL3">

    <h3>Basic Relational Patterns</h3>
    
    

<p>A quick walkthrough of the basic relational patterns.
</p>

    
    <A name="advdatamapping_relation_patterns_onetomany"></a>
    
    <div class="sectionL4">

    <h3>One To Many</h3>
    
    
<p>A one to many relationship places a foreign key in the child table referencing the parent.   SQLAlchemy creates the relationship as a collection on the parent object containing instances of the child object.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">parent_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'parent'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">))</span><span class="python_operator">
</span>
<span class="python_name">child_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'child'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'parent_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'parent.id'</span><span class="python_enclosure">)))</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">Parent</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_keyword">class </span><span class="python_name">Child</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">parent_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">child_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>To establish a bi-directional relationship in one-to-many, where the "reverse" side is a many to one, specify the <code>backref</code> option:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">parent_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">backref</span><span class="python_operator">=</span><span class="python_literal">'parent'</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">child_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p><code>Child</code> will get a <code>parent</code> attribute with many-to-one semantics.
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_patterns_manytoone"></a>
    
    <div class="sectionL4">

    <h3>Many To One</h3>
    
    
<p>Many to one places a foreign key in the parent table referencing the child.  The mapping setup is identical to one-to-many, however SQLAlchemy creates the relationship as a scalar attribute on the parent object referencing a single instance of the child object.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">parent_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'parent'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'child_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'child.id'</span><span class="python_enclosure">)))</span><span class="python_operator">
</span>
<span class="python_name">child_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'child'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">Parent</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_keyword">class </span><span class="python_name">Child</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">parent_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'child'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">child_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Backref behavior is available here as well, where <code>backref=&quot;parents&quot;</code> will place a one-to-many collection on the <code>Child</code> class.
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_patterns_onetoone"></a>
    
    <div class="sectionL4">

    <h3>One To One</h3>
    
    
<p>One To One is essentially a bi-directional relationship with a scalar attribute on both sides.  To achieve this, the <code>uselist=False</code> flag indicates the placement of a scalar attribute instead of a collection on the "many" side of the relationship.  To convert one-to-many into one-to-one:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">parent_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'child'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">uselist</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_operator">, </span><span class="python_name">backref</span><span class="python_operator">=</span><span class="python_literal">'parent'</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>Or to turn many-to-one into one-to-one:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">parent_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'child'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">backref</span><span class="python_operator">=</span><span class="python_name">backref</span><span class="python_enclosure">(</span><span class="python_literal">'parent'</span><span class="python_operator">, </span><span class="python_name">uselist</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">))</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_patterns_manytomany"></a>
    
    <div class="sectionL4">

    <h3>Many To Many</h3>
    
    
<p>Many to Many adds an association table between two classes.  The association table is indicated by the <code>secondary</code> argument to <code>relation()</code>.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">left_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'left'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">))</span><span class="python_operator">
</span>
<span class="python_name">right_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'right'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">))</span><span class="python_operator">
</span>
<span class="python_name">association_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'association'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'left_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'left.id'</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'right_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'right.id'</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">left_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">secondary</span><span class="python_operator">=</span><span class="python_name">association_table</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">right_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>For a bi-directional relationship, both sides of the relation contain a collection by default, which can be modified on either side via the <code>uselist</code> flag to be scalar.  The <code>backref</code> keyword will automatically use the same <code>secondary</code> argument for the reverse relation:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">left_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">secondary</span><span class="python_operator">=</span><span class="python_name">association_table</span><span class="python_operator">, </span><span class="python_name">backref</span><span class="python_operator">=</span><span class="python_literal">'parents'</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_patterns_association"></a>
    
    <div class="sectionL4">

    <h3>Association Object</h3>
    
    
<p>The association object pattern is a variant on many-to-many:  it specifically is used when your association table contains additional columns beyond those which are foreign keys to the left and right tables.  Instead of using the <code>secondary</code> argument, you map a new class directly to the association table.  The left side of the relation references the association object via one-to-many, and the association class references the right side via many-to-one.  <br></br>
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">left_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'left'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">))</span><span class="python_operator">
</span>
<span class="python_name">right_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'right'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">))</span><span class="python_operator">
</span>
<span class="python_name">association_table </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'association'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'left_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'left.id'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'right_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'right.id'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'data'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span>
    <span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">left_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Association</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Association</span><span class="python_operator">, </span><span class="python_name">association_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'child'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">right_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The bi-directional version adds backrefs to both relations:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">left_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Association</span><span class="python_operator">, </span><span class="python_name">backref</span><span class="python_operator">=</span><span class="python_literal">"parent"</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Association</span><span class="python_operator">, </span><span class="python_name">association_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'child'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">backref</span><span class="python_operator">=</span><span class="python_literal">"parent_assocs"</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">right_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>Working with the association pattern in its direct form requires that child objects are associated with an association instance before being appended to the parent; similarly, access from parent to child goes through the association object:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># create parent, append a child via association
</span><span class="python_name">p </span><span class="python_operator">= </span><span class="python_name">Parent</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">a </span><span class="python_operator">= </span><span class="python_name">Association</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">a</span><span class="python_operator">.</span><span class="python_name">child </span><span class="python_operator">= </span><span class="python_name">Child</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">p</span><span class="python_operator">.</span><span class="python_name">children</span><span class="python_operator">.</span><span class="python_name">append</span><span class="python_enclosure">(</span><span class="python_name">a</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># iterate through child objects via association, including association 
# attributes
</span><span class="python_keyword">for </span><span class="python_name">assoc </span><span class="python_keyword">in </span><span class="python_name">p</span><span class="python_operator">.</span><span class="python_name">children</span><span class="python_operator">:
    </span><span class="python_keyword">print </span><span class="python_name">assoc</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">
    </span><span class="python_keyword">print </span><span class="python_name">assoc</span><span class="python_operator">.</span><span class="python_name">child</span><span class="python_operator">
</span></pre>
    </div>
<p>To enhance the association object pattern such that direct access to the <code>Association</code> object is optional, SQLAlchemy provides the <a href="plugins.html#plugins_associationproxy">associationproxy</a>.
</p>
<p><strong>Important Note</strong>:  it is strongly advised that the <code>secondary</code> table argument not be combined with the Association Object pattern, unless the <code>relation()</code> which contains the <code>secondary</code> argument is marked <code>viewonly=True</code>.  Otherwise, SQLAlchemy may persist conflicting data to the underlying association table since it is represented by two conflicting mappings.  The Association Proxy pattern should be favored in the case where access to the underlying association data is only sometimes needed.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="advdatamapping_relation_selfreferential"></a>
    
    <div class="sectionL3">

    <h3>Adjacency List Relationships</h3>
    
    

<p>The <strong>adjacency list</strong> pattern is a common relational pattern whereby a table contains a foreign key reference to itself.  This is the most common and simple way to represent hierarchical data in flat tables.  The other way is the "nested sets" model, sometimes called "modified preorder".  Despite what many online articles say about modified preorder, the adjacency list model is probably the most appropriate pattern for the large majority of hierarchical storage needs, for reasons of concurrency, reduced complexity, and that modified preorder has little advantage over an application which can fully load subtrees into the application space.
</p>
<p>SQLAlchemy commonly refers to an adjacency list relation as a <strong>self-referential mapper</strong>.  In this example, we'll work with a single table called <code>treenodes</code> to represent a tree structure:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">nodes </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'treenodes'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'parent_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'treenodes.id'</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'data'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>A graph such as the following:
</p>

    

    <div class="sliding_code">
        <pre>
root --+---> child1
       +---> child2 --+--> subchild1
       |              +--> subchild2
       +---> child3
</pre>
    </div>
<p>Would be represented with data such as:
</p>

    

    <div class="sliding_code">
        <pre>
id       parent_id     data
---      -------       ----
1        NULL          root
2        1             child1
3        1             child2
4        3             subchild1
5        3             subchild2
6        1             child3
</pre>
    </div>
<p>SQLAlchemy's <code>mapper()</code> configuration for a self-referential one-to-many relationship is exactly like a "normal" one-to-many relationship.  When SQLAlchemy encounters the foreign key relation from <code>treenodes</code> to <code>treenodes</code>, it assumes one-to-many unless told otherwise:    <br></br>
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># entity class
</span><span class="python_keyword">class </span><span class="python_name">Node</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">, </span><span class="python_name">nodes</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>To create a many-to-one relationship from child to parent, an extra indicator of the "remote side" is added, which contains the <code>Column</code> object or objects indicating the remote side of the relation:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">, </span><span class="python_name">nodes</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'parent'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">, </span><span class="python_name">remote_side</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_name">nodes</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">])</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>And the bi-directional version combines both:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">, </span><span class="python_name">nodes</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">, </span><span class="python_name">backref</span><span class="python_operator">=</span><span class="python_name">backref</span><span class="python_enclosure">(</span><span class="python_literal">'parent'</span><span class="python_operator">, </span><span class="python_name">remote_side</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_name">nodes</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_enclosure">]))</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>There are several examples included with SQLAlchemy illustrating self-referential strategies; these include <a href='http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/adjacencytree/basic_tree.py'>basic_tree.py</a> and <a href='http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/elementtree/optimized_al.py'>optimized_al.py</a>, the latter of which illustrates how to persist and search XML documents in conjunction with <a href='http://effbot.org/zone/element-index.htm'>ElementTree</a>.
</p>

    
    <A name="advdatamapping_relation_selfreferential_query"></a>
    
    <div class="sectionL4">

    <h3>Self-Referential Query Strategies</h3>
    
    
<p>Querying self-referential structures is done in the same way as any other query in SQLAlchemy, such as below, we query for any node whose <code>data</code> attrbibute stores the value <code>child2</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># get all nodes named 'child2'
</span><span class="python_name">sess</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">==</span><span class="python_literal">'child2'</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>On the subject of joins, i.e. those described in <a href="ormtutorial.html#datamapping_joins">Querying with Joins</a>, self-referential structures require the usage of aliases so that the same table can be referenced multiple times within the FROM clause of the query.   Aliasing can be done either manually using the <code>nodes</code> <code>Table</code> object as a source of aliases:
</p>

    

    <div class="code">
        <pre>
<span class="python_comment"># get all nodes named 'subchild1' with a parent named 'child2'
</span><span class="python_name">nodealias </span><span class="python_operator">= </span><span class="python_name">nodes</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_1', 'show', 'hide')" class="codepoplink">sql</a></span><span class="python_name">sess</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">==</span><span class="python_literal">'subchild1'</span><span class="python_enclosure">)</span><span class="python_operator">.\
    </span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">and_</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">.</span><span class="python_name">parent_id</span><span class="python_operator">==</span><span class="python_name">nodealias</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">id</span><span class="python_operator">, </span><span class="python_name">nodealias</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">==</span><span class="python_literal">'child2'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_1_div" class="codepop" style="display:none;">SELECT treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data <br/>
FROM treenodes, treenodes AS treenodes_1 <br/>
WHERE treenodes.data = ? AND treenodes.parent_id = treenodes_1.id AND treenodes_1.data = ? ORDER BY treenodes.oid<br/>
['subchild1', 'child2']</div><pre><span class="python_operator"></span></pre>
    </div>
<p>or automatically, using <code>join()</code> with <code>aliased=True</code>:
</p>

    

    <div class="code">
        <pre>
<span class="python_comment"># get all nodes named 'subchild1' with a parent named 'child2'
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_2', 'show', 'hide')" class="codepoplink">sql</a></span><span class="python_name">sess</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">==</span><span class="python_literal">'subchild1'</span><span class="python_enclosure">)</span><span class="python_operator">.\
    </span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_literal">'parent'</span><span class="python_operator">, </span><span class="python_name">aliased</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">==</span><span class="python_literal">'child2'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_2_div" class="codepop" style="display:none;">SELECT treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data <br/>
FROM treenodes JOIN treenodes AS treenodes_1 ON treenodes_1.id = treenodes.parent_id <br/>
WHERE treenodes.data = ? AND treenodes_1.data = ? ORDER BY treenodes.oid<br/>
['subchild1', 'child2']</div><pre><span class="python_operator"></span></pre>
    </div>
<p>To add criterion to multiple points along a longer join, use <code>from_joinpoint=True</code>:
</p>

    

    <div class="code">
        <pre>
<span class="python_comment"># get all nodes named 'subchild1' with a parent named 'child2' and a grandparent 'root'
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_3', 'show', 'hide')" class="codepoplink">sql</a></span><span class="python_name">sess</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">==</span><span class="python_literal">'subchild1'</span><span class="python_enclosure">)</span><span class="python_operator">.\
    </span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_literal">'parent'</span><span class="python_operator">, </span><span class="python_name">aliased</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">==</span><span class="python_literal">'child2'</span><span class="python_enclosure">)</span><span class="python_operator">.\
    </span><span class="python_name">join</span><span class="python_enclosure">(</span><span class="python_literal">'parent'</span><span class="python_operator">, </span><span class="python_name">aliased</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_operator">, </span><span class="python_name">from_joinpoint</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">==</span><span class="python_literal">'root'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_3_div" class="codepop" style="display:none;">SELECT treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data <br/>
FROM treenodes JOIN treenodes AS treenodes_1 ON treenodes_1.id = treenodes.parent_id JOIN treenodes AS treenodes_2 ON treenodes_2.id = treenodes_1.parent_id <br/>
WHERE treenodes.data = ? AND treenodes_1.data = ? AND treenodes_2.data = ? ORDER BY treenodes.oid<br/>
['subchild1', 'child2', 'root']</div><pre><span class="python_operator"></span></pre>
    </div>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_selfreferential_eagerloading"></a>
    
    <div class="sectionL4">

    <h3>Configuring Eager Loading</h3>
    
    
<p>Eager loading of relations occurs using joins or outerjoins from parent to child table during a normal query operation, such that the parent and its child collection can be populated from a single SQL statement.  SQLAlchemy's eager loading uses aliased tables in all cases when joining to related items, so it is compatible with self-referential joining.  However, to use eager loading with a self-referential relation, SQLAlchemy needs to be told how many levels deep it should join; otherwise the eager load will not take place.  This depth setting is configured via <code>join_depth</code>:
</p>

    

    <div class="code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">, </span><span class="python_name">nodes</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_operator">, </span><span class="python_name">lazy</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_operator">, </span><span class="python_name">join_depth</span><span class="python_operator">=</span><span class="python_number">2</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_4', 'show', 'hide')" class="codepoplink">sql</a></span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Node</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_4_div" class="codepop" style="display:none;">SELECT treenodes_1.id AS treenodes_1_id, treenodes_1.parent_id AS treenodes_1_parent_id, treenodes_1.data AS treenodes_1_data, treenodes_2.id AS treenodes_2_id, treenodes_2.parent_id AS treenodes_2_parent_id, treenodes_2.data AS treenodes_2_data, treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data <br/>
FROM treenodes LEFT OUTER JOIN treenodes AS treenodes_2 ON treenodes.id = treenodes_2.parent_id LEFT OUTER JOIN treenodes AS treenodes_1 ON treenodes_2.id = treenodes_1.parent_id ORDER BY treenodes.oid, treenodes_2.oid, treenodes_1.oid<br/>
[]</div><pre><span class="python_operator"></span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="advdatamapping_relation_customjoin"></a>
    
    <div class="sectionL3">

    <h3>Specifying Alternate Join Conditions to relation()</h3>
    
    

<p>The <code>relation()</code> function uses the foreign key relationship between the parent and child tables to formulate the <strong>primary join condition</strong> between parent and child; in the case of a many-to-many relationship it also formulates the <strong>secondary join condition</strong>.  If you are working with a <code>Table</code> which has no <code>ForeignKey</code> objects on it (which can be the case when using reflected tables with MySQL), or if the join condition cannot be expressed by a simple foreign key relationship, use the <code>primaryjoin</code> and possibly <code>secondaryjoin</code> conditions to create the appropriate relationship.
</p>
<p>In this example we create a relation <code>boston_addresses</code> which will only load the user addresses with a city of "Boston":
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">User</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span><span class="python_keyword">class </span><span class="python_name">Address</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'boston_addresses' </span><span class="python_operator">: </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">primaryjoin</span><span class="python_operator">=</span>
                <span class="python_name">and_</span><span class="python_enclosure">(</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span>
                <span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">city</span><span class="python_operator">==</span><span class="python_literal">'Boston'</span><span class="python_enclosure">))</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>Many to many relationships can be customized by one or both of <code>primaryjoin</code> and <code>secondaryjoin</code>, shown below with just the default many-to-many relationship explicitly set:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">User</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span><span class="python_keyword">class </span><span class="python_name">Keyword</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Keyword</span><span class="python_operator">, </span><span class="python_name">keywords_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'keywords'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Keyword</span><span class="python_operator">, </span><span class="python_name">secondary</span><span class="python_operator">=</span><span class="python_name">userkeywords_table</span><span class="python_operator">,</span>
        <span class="python_name">primaryjoin</span><span class="python_operator">=</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_name">userkeywords_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">,</span>
        <span class="python_name">secondaryjoin</span><span class="python_operator">=</span><span class="python_name">userkeywords_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">keyword_id</span><span class="python_operator">==</span><span class="python_name">keywords_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">keyword_id</span>
        <span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>

    
    <A name="advdatamapping_relation_customjoin_fks"></a>
    
    <div class="sectionL4">

    <h3>Specifying Foreign Keys</h3>
    
    
<p>When using <code>primaryjoin</code> and <code>secondaryjoin</code>, SQLAlchemy also needs to be aware of which columns in the relation reference the other.  In most cases, a <code>Table</code> construct will have <code>ForeignKey</code> constructs which take care of this; however, in the case of reflected tables on a database that does not report FKs (like MySQL ISAM) or when using join conditions on columns that don't have foreign keys, the <code>relation()</code> needs to be told specifically which columns are "foreign" using the <code>foreign_keys</code> collection:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'addresses' </span><span class="python_operator">: </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span>
         <span class="python_name">primaryjoin</span><span class="python_operator">=</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">,</span>
         <span class="python_name">foreign_keys</span><span class="python_operator">=</span><span class="python_enclosure">[</span><span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_enclosure">])</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_customjoin_properties"></a>
    
    <div class="sectionL4">

    <h3>Building Query-Enabled Properties</h3>
    
    
<p>Very ambitious custom join conditions may fail to be directly persistable, and in some cases may not even load correctly.  To remove the persistence part of the equation, use the flag <code>viewonly=True</code> on the <code>relation()</code>, which establishes it as a read-only attribute (data written to the collection will be ignored on flush()).  However, in extreme cases, consider using a regular Python property in conjunction with <code>Query</code> as follows:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">User</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">def </span><span class="python_name">_get_addresses</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_name">object_session</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">with_parent</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_operator">...</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
    </span><span class="python_name">addresses </span><span class="python_operator">= </span><span class="python_name">property</span><span class="python_enclosure">(</span><span class="python_name">_get_addresses</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_customjoin_multiplejoin"></a>
    
    <div class="sectionL4">

    <h3>Multiple Relations against the Same Parent/Child</h3>
    
    
<p>Theres no restriction on how many times you can relate from parent to child.  SQLAlchemy can usually figure out what you want, particularly if the join conditions are straightforward.  Below we add a <code>newyork_addresses</code> attribute to complement the <code>boston_addresses</code> attribute:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'boston_addresses' </span><span class="python_operator">: </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">primaryjoin</span><span class="python_operator">=</span>
                <span class="python_name">and_</span><span class="python_enclosure">(</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_name">Address</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span>
                <span class="python_name">Addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">city</span><span class="python_operator">==</span><span class="python_literal">'Boston'</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_literal">'newyork_addresses' </span><span class="python_operator">: </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">primaryjoin</span><span class="python_operator">=</span>
                <span class="python_name">and_</span><span class="python_enclosure">(</span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_name">Address</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">, </span>
                <span class="python_name">Addresses</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">city</span><span class="python_operator">==</span><span class="python_literal">'New York'</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="advdatamapping_relation_collections"></a>
    
    <div class="sectionL3">

    <h3>Alternate Collection Implementations</h3>
    
    

<p>Mapping a one-to-many or many-to-many relationship results in a collection of values accessible through an attribute on the parent instance.  By default, this collection is a <code>list</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_name">children </span><span class="python_operator">= </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">parent </span><span class="python_operator">= </span><span class="python_name">Parent</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">parent</span><span class="python_operator">.</span><span class="python_name">children</span><span class="python_operator">.</span><span class="python_name">append</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_enclosure">())</span><span class="python_operator">
</span><span class="python_keyword">print </span><span class="python_name">parent</span><span class="python_operator">.</span><span class="python_name">children</span><span class="python_enclosure">[</span><span class="python_number">0</span><span class="python_enclosure">]</span><span class="python_operator">
</span></pre>
    </div>
<p>Collections are not limited to lists.  Sets, mutable sequences and almost any other Python object that can act as a container can be used in place of the default list.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># use a set
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_name">children </span><span class="python_operator">= </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">collection_class</span><span class="python_operator">=</span><span class="python_name">set</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">parent </span><span class="python_operator">= </span><span class="python_name">Parent</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">child </span><span class="python_operator">= </span><span class="python_name">Child</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">parent</span><span class="python_operator">.</span><span class="python_name">children</span><span class="python_operator">.</span><span class="python_name">add</span><span class="python_enclosure">(</span><span class="python_name">child</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_keyword">assert </span><span class="python_name">child </span><span class="python_keyword">in </span><span class="python_name">parent</span><span class="python_operator">.</span><span class="python_name">children</span><span class="python_operator">
</span></pre>
    </div>

    
    <A name="advdatamapping_relation_collections_custom"></a>
    
    <div class="sectionL4">

    <h3>Custom Collection Implementations</h3>
    
    
<p>You can use your own types for collections as well.  For most cases, simply inherit from <code>list</code> or <code>set</code> and add the custom behavior.
</p>
<p>Collections in SQLAlchemy are transparently <em>instrumented</em>.  Instrumentation means that normal operations on the collection are tracked and result in changes being written to the database at flush time.  Additionally, collection operations can fire <em>events</em> which indicate some secondary operation must take place.  Examples of a secondary operation include saving the child item in the parent's <code>Session</code> (i.e. the <code>save-update</code> cascade), as well as synchronizing the state of a bi-directional relationship (i.e. a <code>backref</code>).
</p>
<p>The collections package understands the basic interface of lists, sets and dicts and will automatically apply instrumentation to those built-in types and their subclasses.  Object-derived types that implement a basic collection interface are detected and instrumented via duck-typing:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">ListLike</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">def </span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data </span><span class="python_operator">= </span><span class="python_enclosure">[]</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">append</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">.</span><span class="python_name">append</span><span class="python_enclosure">(</span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">remove</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">.</span><span class="python_name">remove</span><span class="python_enclosure">(</span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">extend</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">items</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">.</span><span class="python_name">extend</span><span class="python_enclosure">(</span><span class="python_name">items</span><span class="python_enclosure">)</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">__iter__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_name">iter</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_enclosure">)</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">foo</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_literal">'foo'</span><span class="python_operator">
</span></pre>
    </div>
<p><code>append</code>, <code>remove</code>, and <code>extend</code> are known list-like methods, and will be instrumented automatically.  <code>__iter__</code> is not a mutator method and won't be instrumented, and <code>foo</code> won't be either.
</p>
<p>Duck-typing (i.e. guesswork) isn't rock-solid, of course, so you can be explicit about the interface you are implementing by providing an <code>__emulates__</code> class attribute:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">SetLike</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_name">__emulates__ </span><span class="python_operator">= </span><span class="python_name">set</span><span class="python_operator">
</span>
    <span class="python_keyword">def </span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data </span><span class="python_operator">= </span><span class="python_name">set</span><span class="python_enclosure">()</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">append</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">.</span><span class="python_name">add</span><span class="python_enclosure">(</span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">remove</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">.</span><span class="python_name">remove</span><span class="python_enclosure">(</span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">__iter__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_name">iter</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>This class looks list-like because of <code>append</code>, but <code>__emulates__</code> forces it to set-like.  <code>remove</code> is known to be part of the set interface and will be instrumented.
</p>
<p>But this class won't work quite yet: a little glue is needed to adapt it for use by SQLAlchemy.  The ORM needs to know which methods to use to append, remove and iterate over members of the collection.  When using a type like <code>list</code> or <code>set</code>, the appropriate methods are well-known and used automatically when present. This set-like class does not provide the expected <code>add</code> method, so we must supply an explicit mapping for the ORM via a decorator.
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_collections_decorators"></a>
    
    <div class="sectionL4">

    <h3>Annotating Custom Collections via Decorators</h3>
    
    
<p>Decorators can be used to tag the individual methods the ORM needs to manage collections.  Use them when your class doesn't quite meet the regular interface for its container type, or you simply would like to use a different method to get the job done.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">orm</span><span class="python_operator">.</span><span class="python_name">collections </span><span class="python_keyword">import </span><span class="python_name">collection</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">SetLike</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_name">__emulates__ </span><span class="python_operator">= </span><span class="python_name">set</span><span class="python_operator">
</span>
    <span class="python_keyword">def </span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data </span><span class="python_operator">= </span><span class="python_name">set</span><span class="python_enclosure">()</span><span class="python_operator">
</span>
    <span class="python_operator">@</span><span class="python_name">collection</span><span class="python_operator">.</span><span class="python_name">appender</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">append</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">.</span><span class="python_name">add</span><span class="python_enclosure">(</span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
    <span class="python_operator"></span><span class="python_keyword">def </span><span class="python_name">remove</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_operator">.</span><span class="python_name">remove</span><span class="python_enclosure">(</span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
    <span class="python_operator"></span><span class="python_keyword">def </span><span class="python_name">__iter__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_keyword">return </span><span class="python_name">iter</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">.</span><span class="python_name">data</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>And that's all that's needed to complete the example.  SQLAlchemy will add instances via the <code>append</code> method.  <code>remove</code> and <code>__iter__</code> are the default methods for sets and will be used for removing and iteration.  Default methods can be changed as well:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">orm</span><span class="python_operator">.</span><span class="python_name">collections </span><span class="python_keyword">import </span><span class="python_name">collection</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">MyList</span><span class="python_enclosure">(</span><span class="python_name">list</span><span class="python_enclosure">)</span><span class="python_operator">:
    @</span><span class="python_name">collection</span><span class="python_operator">.</span><span class="python_name">remover</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">zark</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">item</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_comment"># do something special...
</span>
    <span class="python_operator">@</span><span class="python_name">collection</span><span class="python_operator">.</span><span class="python_name">iterator</span><span class="python_operator">
    </span><span class="python_keyword">def </span><span class="python_name">hey_use_this_instead_for_iteration</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_comment"># ...
</span><span class="python_operator"></span></pre>
    </div>
<p>There is no requirement to be list-, or set-like at all.  Collection classes can be any shape, so long as they have the append, remove and iterate interface marked for SQLAlchemy's use.  Append and remove methods will be called with a mapped entity as the single argument, and iterator methods are called with no arguments and must return an iterator.
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_collections_dictcollections"></a>
    
    <div class="sectionL4">

    <h3>Dictionary-Based Collections</h3>
    
    
<p>A <code>dict</code> can be used as a collection, but a keying strategy is needed to map entities loaded by the ORM to key, value pairs.  The <a href="sqlalchemy_orm_collections.html#docstrings_sqlalchemy.orm.collections">collections</a> package provides several built-in types for dictionary-based collections:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">orm</span><span class="python_operator">.</span><span class="python_name">collections </span><span class="python_keyword">import </span><span class="python_name">column_mapped_collection</span><span class="python_operator">, </span><span class="python_name">attribute_mapped_collection</span><span class="python_operator">, </span><span class="python_name">mapped_collection</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Item</span><span class="python_operator">, </span><span class="python_name">items_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_comment"># key by column</span>
    <span class="python_literal">'notes'</span><span class="python_operator">: </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Note</span><span class="python_operator">, </span><span class="python_name">collection_class</span><span class="python_operator">=</span><span class="python_name">column_mapped_collection</span><span class="python_enclosure">(</span><span class="python_name">notes_table</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">keyword</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_comment"># or named attribute </span>
    <span class="python_literal">'notes2'</span><span class="python_operator">: </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Note</span><span class="python_operator">, </span><span class="python_name">collection_class</span><span class="python_operator">=</span><span class="python_name">attribute_mapped_collection</span><span class="python_enclosure">(</span><span class="python_literal">'keyword'</span><span class="python_enclosure">))</span><span class="python_operator">,</span>
    <span class="python_comment"># or any callable</span>
    <span class="python_literal">'notes3'</span><span class="python_operator">: </span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Note</span><span class="python_operator">, </span><span class="python_name">collection_class</span><span class="python_operator">=</span><span class="python_name">mapped_collection</span><span class="python_enclosure">(</span><span class="python_keyword">lambda </span><span class="python_name">entity</span><span class="python_operator">: </span><span class="python_name">entity</span><span class="python_operator">.</span><span class="python_name">a </span><span class="python_operator">+ </span><span class="python_name">entity</span><span class="python_operator">.</span><span class="python_name">b</span><span class="python_enclosure">))</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_comment"># ...
</span><span class="python_name">item </span><span class="python_operator">= </span><span class="python_name">Item</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">item</span><span class="python_operator">.</span><span class="python_name">notes</span><span class="python_enclosure">[</span><span class="python_literal">'color'</span><span class="python_enclosure">] </span><span class="python_operator">= </span><span class="python_name">Note</span><span class="python_enclosure">(</span><span class="python_literal">'color'</span><span class="python_operator">, </span><span class="python_literal">'blue'</span><span class="python_enclosure">)</span><span class="python_operator">
</span><span class="python_keyword">print </span><span class="python_name">item</span><span class="python_operator">.</span><span class="python_name">notes</span><span class="python_enclosure">[</span><span class="python_literal">'color'</span><span class="python_enclosure">]</span><span class="python_operator">
</span></pre>
    </div>
<p>These functions each provide a <code>dict</code> subclass with decorated <code>set</code> and <code>remove</code> methods and the keying strategy of your choice.
</p>
<p>The <b>collections.MappedCollection</b> class can be used as a base class for your custom types or as a mix-in to quickly add <code>dict</code> collection support to other classes.  It uses a keying function to delegate to <code>__setitem__</code> and <code>__delitem__</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">util </span><span class="python_keyword">import </span><span class="python_name">OrderedDict</span><span class="python_operator">
</span><span class="python_keyword">from </span><span class="python_name">sqlalchemy</span><span class="python_operator">.</span><span class="python_name">orm</span><span class="python_operator">.</span><span class="python_name">collections </span><span class="python_keyword">import </span><span class="python_name">MappedCollection</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">NodeMap</span><span class="python_enclosure">(</span><span class="python_name">OrderedDict</span><span class="python_operator">, </span><span class="python_name">MappedCollection</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_literal">"""Holds 'Node' objects, keyed by the 'name' attribute with insert order maintained."""</span><span class="python_operator">
</span>
    <span class="python_keyword">def </span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, *</span><span class="python_name">args</span><span class="python_operator">, **</span><span class="python_name">kw</span><span class="python_enclosure">)</span><span class="python_operator">:
        </span><span class="python_name">MappedCollection</span><span class="python_operator">.</span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, </span><span class="python_name">keyfunc</span><span class="python_operator">=</span><span class="python_keyword">lambda </span><span class="python_name">node</span><span class="python_operator">: </span><span class="python_name">node</span><span class="python_operator">.</span><span class="python_name">name</span><span class="python_enclosure">)</span><span class="python_operator">
        </span><span class="python_name">OrderedDict</span><span class="python_operator">.</span><span class="python_name">__init__</span><span class="python_enclosure">(</span><span class="python_name">self</span><span class="python_operator">, *</span><span class="python_name">args</span><span class="python_operator">, **</span><span class="python_name">kw</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>The ORM understands the <code>dict</code> interface just like lists and sets, and will automatically instrument all dict-like methods if you choose to subclass <code>dict</code> or provide dict-like collection behavior in a duck-typed class.  You must decorate appender and remover methods, however- there are no compatible methods in the basic dictionary interface for SQLAlchemy to use by default.  Iteration will go through <code>itervalues()</code> unless otherwise decorated.
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_collections_adv_collections"></a>
    
    <div class="sectionL4">

    <h3>Instrumentation and Custom Types</h3>
    
    
<p>Many custom types and existing library classes can be used as a entity collection type as-is without further ado.  However, it is important to note that the instrumentation process <em>will</em> modify the type, adding decorators around methods automatically.
</p>
<p>The decorations are lightweight and no-op outside of relations, but they do add unneeded overhead when triggered elsewhere.  When using a library class as a collection, it can be good practice to use the "trivial subclass" trick to restrict the decorations to just your usage in relations.  For example:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_keyword">class </span><span class="python_name">MyAwesomeList</span><span class="python_enclosure">(</span><span class="python_name">some</span><span class="python_operator">.</span><span class="python_name">great</span><span class="python_operator">.</span><span class="python_name">library</span><span class="python_operator">.</span><span class="python_name">AwesomeList</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_comment"># ... relation(..., collection_class=MyAwesomeList)
</span><span class="python_operator"></span></pre>
    </div>
<p>The ORM uses this approach for built-ins, quietly substituting a trivial subclass when a <code>list</code>, <code>set</code> or <code>dict</code> is used directly.
</p>
<p>The collections package provides additional decorators and support for authoring custom types.  See the <a href="sqlalchemy_orm_collections.html#docstrings_sqlalchemy.orm.collections">package documentation</a> for more information and discussion of advanced usage and Python 2.3-compatible decoration options.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="advdatamapping_relation_strategies"></a>
    
    <div class="sectionL3">

    <h3>Configuring Loader Strategies: Lazy Loading, Eager Loading</h3>
    
    

<p>In the <a href="ormtutorial.html">Object Relational Tutorial</a>, we introduced the concept of <strong>Eager Loading</strong>.  We used an <code>option</code> in conjunction with the <code>Query</code> object in order to indicate that a relation should be loaded at the same time as the parent, within a single SQL query:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_5', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">jack </span><span class="python_operator">= </span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">eagerload</span><span class="python_enclosure">(</span><span class="python_literal">'addresses'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">filter_by</span><span class="python_enclosure">(</span><span class="python_name">name</span><span class="python_operator">=</span><span class="python_literal">'jack'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator"> </span></pre><div id="popbox_5_div" class="codepop" style="display:none;">SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, <br/>
addresses_1.user_id AS addresses_1_user_id, users.id AS users_id, users.name AS users_name, <br/>
users.fullname AS users_fullname, users.password AS users_password <br/>
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id <br/>
WHERE users.name = ? ORDER BY users.oid, addresses_1.oid<br/>
['jack']</div><pre><span class="python_operator"></span></pre>
    </div>
<p>By default, all relations are <strong>lazy loading</strong>.  The scalar or collection attribute associated with a <code>relation()</code> contains a trigger which fires the first time the attribute is accessed, which issues a SQL call at that point:
</p>

    

    <div class="code">
        <pre>
<span class="python_literal"><a href="javascript:togglePopbox('popbox_6', 'show', 'hide')" class="codepoplink">sql</a>&gt;&gt;&gt; </span><span class="python_name">jack</span><span class="python_operator">.</span><span class="python_name">addresses</span><span class="python_operator"></span></pre><div id="popbox_6_div" class="codepop" style="display:none;">SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id <br/>
FROM addresses <br/>
WHERE ? = addresses.user_id ORDER BY addresses.oid<br/>
[5]</div><pre><span class="python_enclosure">[</span><span class="python_operator">&lt;</span><span class="python_name">Address</span><span class="python_enclosure">(</span><span class="python_literal">u'jack@google.com'</span><span class="python_enclosure">)</span><span class="python_operator">&gt;, &lt;</span><span class="python_name">Address</span><span class="python_enclosure">(</span><span class="python_literal">u'j25@yahoo.com'</span><span class="python_enclosure">)</span><span class="python_operator">&gt;</span><span class="python_enclosure">]</span><span class="python_operator">
</span></pre>
    </div>
<p>The default <strong>loader strategy</strong> for any <code>relation()</code> is configured by the <code>lazy</code> keyword argument, which defaults to <code>True</code>.  Below we set it as <code>False</code> so that the <code>children</code> relation is eager loading:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># eager load 'children' attribute
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_operator">, </span><span class="python_name">parent_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Child</span><span class="python_operator">, </span><span class="python_name">lazy</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>The loader strategy can be changed from lazy to eager as well as eager to lazy using the <code>eagerload()</code> and <code>lazyload()</code> query options:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># set children to load lazily
</span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">lazyload</span><span class="python_enclosure">(</span><span class="python_literal">'children'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span>
<span class="python_comment"># set children to load eagerly
</span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">eagerload</span><span class="python_enclosure">(</span><span class="python_literal">'children'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>To reference a relation that is deeper than one level, separate the names by periods:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">Parent</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">eagerload</span><span class="python_enclosure">(</span><span class="python_literal">'foo.bar.bat'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>When using dot-separated names with <code>eagerload()</code>, option applies <strong>only</strong> to the actual attribute named, and <strong>not</strong> its ancestors.  For example, suppose a mapping from <code>A</code> to <code>B</code> to <code>C</code>, where the relations, named <code>atob</code> and <code>btoc</code>, are both lazy-loading.  A statement like the following:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">A</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">eagerload</span><span class="python_enclosure">(</span><span class="python_literal">'atob.btoc'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>will load only <code>A</code> objects to start.  When the <code>atob</code> attribute on each <code>A</code> is accessed, the returned <code>B</code> objects will <em>eagerly</em> load their <code>C</code> objects. 
</p>
<p>Therefore, to modify the eager load to load both <code>atob</code> as well as <code>btoc</code>, place eagerloads for both:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">A</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">eagerload</span><span class="python_enclosure">(</span><span class="python_literal">'atob'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">eagerload</span><span class="python_enclosure">(</span><span class="python_literal">'atob.btoc'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>or more simply just use <code>eagerload_all()</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">A</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">eagerload_all</span><span class="python_enclosure">(</span><span class="python_literal">'atob.btoc'</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator">
</span></pre>
    </div>
<p>There are two other loader strategies available, <strong>dynamic loading</strong> and <strong>no loading</strong>; these are described in <a href="mappers.html#advdatamapping_relation_largecollections">Working with Large Collections</a>.
</p>

    
    <A name="advdatamapping_relation_strategies_combining"></a>
    
    <div class="sectionL4">

    <h3>Combining Eager Loads with Statement/Result Set Queries</h3>
    
    
<p>When full statement or result-set loads are used with <code>Query</code>, SQLAlchemy does not affect the SQL query itself, and therefore has no way of tacking on its own <code>LEFT [OUTER] JOIN</code> conditions that are normally used to eager load relationships.  If the query being constructed is created in such a way that it returns rows not just from a parent table (or tables) but also returns rows from child tables, the result-set mapping can be notified as to which additional properties are contained within the result set.  This is done using the <code>contains_eager()</code> query option, which specifies the name of the relationship to be eagerly loaded.
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># mapping is the users-&gt;addresses mapping
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'addresses'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">addresses_table</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_comment"># define a query on USERS with an outer join to ADDRESSES
</span><span class="python_name">statement </span><span class="python_operator">= </span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">addresses_table</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">use_labels</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># construct a Query object which expects the "addresses" results 
</span><span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">contains_eager</span><span class="python_enclosure">(</span><span class="python_literal">'addresses'</span><span class="python_enclosure">))</span><span class="python_operator">
</span>
<span class="python_comment"># get results normally
</span><span class="python_name">r </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">from_statement</span><span class="python_enclosure">(</span><span class="python_name">statement</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>If the "eager" portion of the statement is "aliased", the <code>alias</code> keyword argument to <code>contains_eager()</code> may be used to indicate it.  This is a string alias name or reference to an actual <code>Alias</code> object:
</p>

    

    <div class="code">
        <pre>
<span class="python_comment"># use an alias of the addresses table
</span><span class="python_name">adalias </span><span class="python_operator">= </span><span class="python_name">addresses_table</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">(</span><span class="python_literal">'adalias'</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># define a query on USERS with an outer join to adalias
</span><span class="python_name">statement </span><span class="python_operator">= </span><span class="python_name">users_table</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">adalias</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">use_labels</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># construct a Query object which expects the "addresses" results 
</span><span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">contains_eager</span><span class="python_enclosure">(</span><span class="python_literal">'addresses'</span><span class="python_operator">, </span><span class="python_name">alias</span><span class="python_operator">=</span><span class="python_name">adalias</span><span class="python_enclosure">))</span><span class="python_operator">
</span>
<span class="python_comment"># get results normally
</span><span class="python_literal"><a href="javascript:togglePopbox('popbox_7', 'show', 'hide')" class="codepoplink">sql</a></span><span class="python_name">r </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">from_statement</span><span class="python_enclosure">(</span><span class="python_name">statement</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">all</span><span class="python_enclosure">()</span><span class="python_operator"></span></pre><div id="popbox_7_div" class="codepop" style="display:none;">SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, adalias.address_id AS adalias_address_id, <br/>
adalias.user_id AS adalias_user_id, adalias.email_address AS adalias_email_address, (...other columns...)<br/>
FROM users LEFT OUTER JOIN email_addresses AS adalias ON users.user_id = adalias.user_id</div><pre><span class="python_operator"></span></pre>
    </div>
<p>In the case that the main table itself is also aliased, the <code>contains_alias()</code> option can be used:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_comment"># define an aliased UNION called 'ulist'
</span><span class="python_name">statement </span><span class="python_operator">= </span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">==</span><span class="python_number">7</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">union</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">users</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">user_id</span><span class="python_operator">&gt;</span><span class="python_number">7</span><span class="python_enclosure">))</span><span class="python_operator">.</span><span class="python_name">alias</span><span class="python_enclosure">(</span><span class="python_literal">'ulist'</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># add on an eager load of "addresses"
</span><span class="python_name">statement </span><span class="python_operator">= </span><span class="python_name">statement</span><span class="python_operator">.</span><span class="python_name">outerjoin</span><span class="python_enclosure">(</span><span class="python_name">addresses</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">select</span><span class="python_enclosure">(</span><span class="python_name">use_labels</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># create query, indicating "ulist" is an alias for the main table, "addresses" property should
# be eager loaded
</span><span class="python_name">query </span><span class="python_operator">= </span><span class="python_name">create_session</span><span class="python_enclosure">()</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">options</span><span class="python_enclosure">(</span><span class="python_name">contains_alias</span><span class="python_enclosure">(</span><span class="python_literal">'ulist'</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">contains_eager</span><span class="python_enclosure">(</span><span class="python_literal">'addresses'</span><span class="python_enclosure">))</span><span class="python_operator">
</span>
<span class="python_comment"># results
</span><span class="python_name">r </span><span class="python_operator">= </span><span class="python_name">query</span><span class="python_operator">.</span><span class="python_name">from_statement</span><span class="python_enclosure">(</span><span class="python_name">statement</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="advdatamapping_relation_largecollections"></a>
    
    <div class="sectionL3">

    <h3>Working with Large Collections</h3>
    
    

<p>The default behavior of <code>relation()</code> is to fully load the collection of items in, as according to the loading strategy of the relation.  Additionally, the Session by default only knows how to delete objects which are actually present within the session.  When a parent instance is marked for deletion and flushed, the Session loads its full list of child items in so that they may either be deleted as well, or have their foreign key value set to null; this is to avoid constraint violations.  For large collections of child items, there are several strategies to bypass full loading of child items both at load time as well as deletion time.
</p>

    
    <A name="advdatamapping_relation_largecollections_dynamic"></a>
    
    <div class="sectionL4">

    <h3>Dynamic Relation Loaders</h3>
    
    
<p>The most useful by far is the <code>dynamic_loader()</code> relation.  This is a variant of <code>relation()</code> which returns a <code>Query</code> object in place of a collection when accessed.  <code>filter()</code> criterion may be applied as well as limits and offsets, either explicitly or via array slices:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'posts'</span><span class="python_operator">:</span><span class="python_name">dynamic_loader</span><span class="python_enclosure">(</span><span class="python_name">Post</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span>
<span class="python_name">jack </span><span class="python_operator">= </span><span class="python_name">session</span><span class="python_operator">.</span><span class="python_name">query</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">get</span><span class="python_enclosure">(</span><span class="python_name">id</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># filter Jack's blog posts
</span><span class="python_name">posts </span><span class="python_operator">= </span><span class="python_name">jack</span><span class="python_operator">.</span><span class="python_name">posts</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Post</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">headline</span><span class="python_operator">==</span><span class="python_literal">'this is a post'</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_comment"># apply array slices
</span><span class="python_name">posts </span><span class="python_operator">= </span><span class="python_name">jack</span><span class="python_operator">.</span><span class="python_name">posts</span><span class="python_enclosure">[</span><span class="python_number">5</span><span class="python_operator">:</span><span class="python_number">20</span><span class="python_enclosure">]</span><span class="python_operator">
</span></pre>
    </div>
<p>The dynamic relation supports limited write operations, via the <code>append()</code> and <code>remove()</code> methods.  Since the read side of the dynamic relation always queries the database, changes to the underlying collection will not be visible until the data has been flushed:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">oldpost </span><span class="python_operator">= </span><span class="python_name">jack</span><span class="python_operator">.</span><span class="python_name">posts</span><span class="python_operator">.</span><span class="python_name">filter</span><span class="python_enclosure">(</span><span class="python_name">Post</span><span class="python_operator">.</span><span class="python_name">c</span><span class="python_operator">.</span><span class="python_name">headline</span><span class="python_operator">==</span><span class="python_literal">'old post'</span><span class="python_enclosure">)</span><span class="python_operator">.</span><span class="python_name">one</span><span class="python_enclosure">()</span><span class="python_operator">
</span><span class="python_name">jack</span><span class="python_operator">.</span><span class="python_name">posts</span><span class="python_operator">.</span><span class="python_name">remove</span><span class="python_enclosure">(</span><span class="python_name">oldpost</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">jack</span><span class="python_operator">.</span><span class="python_name">posts</span><span class="python_operator">.</span><span class="python_name">append</span><span class="python_enclosure">(</span><span class="python_name">Post</span><span class="python_enclosure">(</span><span class="python_literal">'new post'</span><span class="python_enclosure">))</span><span class="python_operator">
</span></pre>
    </div>
<p>To place a dynamic relation on a backref, use <code>lazy='dynamic'</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Post</span><span class="python_operator">, </span><span class="python_name">posts_table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'user'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">backref</span><span class="python_operator">=</span><span class="python_name">backref</span><span class="python_enclosure">(</span><span class="python_literal">'posts'</span><span class="python_operator">, </span><span class="python_name">lazy</span><span class="python_operator">=</span><span class="python_literal">'dynamic'</span><span class="python_enclosure">))</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>Note that eager/lazy loading options cannot be used in conjunction dynamic relations at this time.
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_largecollections_noload"></a>
    
    <div class="sectionL4">

    <h3>Setting Noload</h3>
    
    
<p>The opposite of the dynamic relation is simply "noload", specified using <code>lazy=None</code>:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">MyClass</span><span class="python_operator">, </span><span class="python_name">table</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_name">relation</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">MyOtherClass</span><span class="python_operator">, </span><span class="python_name">lazy</span><span class="python_operator">=</span><span class="python_name">None</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>Above, the <code>children</code> collection is fully writeable, and changes to it will be persisted to the database as well as locally available for reading at the time they are added.  However when instances of  <code>MyClass</code> are freshly loaded from the database, the <code>children</code> collection stays empty.
</p>


            <a href="#top" class="totoc">back to section top</a>
    </div>



    
    <A name="advdatamapping_relation_largecollections_passivedelete"></a>
    
    <div class="sectionL4">

    <h3>Using Passive Deletes</h3>
    
    
<p>Use <code>passive_deletes=True</code> to disable child object loading on a DELETE operation, in conjunction with "ON DELETE (CASCADE|SET NULL)" on your database to automatically cascade deletes to child objects.   Note that "ON DELETE" is not supported on SQLite, and requires <code>InnoDB</code> tables when using MySQL:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">mytable </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'mytable'</span><span class="python_operator">, </span><span class="python_name">meta</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">myothertable </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'myothertable'</span><span class="python_operator">, </span><span class="python_name">meta</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'parent_id'</span><span class="python_operator">, </span><span class="python_name">Integer</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">ForeignKeyConstraint</span><span class="python_enclosure">([</span><span class="python_literal">'parent_id'</span><span class="python_enclosure">]</span><span class="python_operator">,</span><span class="python_enclosure">[</span><span class="python_literal">'mytable.id'</span><span class="python_enclosure">]</span><span class="python_operator">, </span><span class="python_name">ondelete</span><span class="python_operator">=</span><span class="python_literal">"CASCADE"</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">mmapper</span><span class="python_enclosure">(</span><span class="python_name">MyOtherClass</span><span class="python_operator">, </span><span class="python_name">myothertable</span><span class="python_enclosure">)</span><span class="python_operator">
</span>
<span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">MyClass</span><span class="python_operator">, </span><span class="python_name">mytable</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'children'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">MyOtherClass</span><span class="python_operator">, </span><span class="python_name">cascade</span><span class="python_operator">=</span><span class="python_literal">"all, delete-orphan"</span><span class="python_operator">, </span><span class="python_name">passive_deletes</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span></pre>
    </div>
<p>When <code>passive_deletes</code> is applied, the <code>children</code> relation will not be loaded into memory when an instance of <code>MyClass</code> is marked for deletion.  The <code>cascade=&quot;all, delete-orphan&quot;</code> <em>will</em> take effect for instances of <code>MyOtherClass</code> which are currently present in the session; however for instances of <code>MyOtherClass</code> which are not loaded, SQLAlchemy assumes that "ON DELETE CASCADE" rules will ensure that those rows are deleted by the database and that no foreign key violation will occur.
</p>



            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>



    
    <A name="advdatamapping_relation_mutablepks"></a>
    
    <div class="sectionL3">

    <h3>Mutable Primary Keys / Update Cascades</h3>
    
    

<p>As of SQLAlchemy 0.4.2, the primary key attributes of an instance can be changed freely, and will be persisted upon flush.  When the primary key of an entity changes, related items which reference the primary key must also be updated as well.  For databases which enforce referential integrity, it's required to use the database's ON UPDATE CASCADE functionality in order to propagate primary key changes.  For those which don't, the <code>passive_cascades</code> flag can be set to <code>False</code> which instructs SQLAlchemy to issue UPDATE statements individually.  The <code>passive_cascades</code> flag can also be <code>False</code> in conjunction with ON UPDATE CASCADE functionality, although in that case it issues UPDATE statements unnecessarily.
</p>
<p>A typical mutable primary key setup might look like:
</p>

    

    <div class="sliding_code">
        <pre>
<span class="python_name">users </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'users'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'username'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'fullname'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">100</span><span class="python_enclosure">)))</span><span class="python_operator">
</span>
<span class="python_name">addresses </span><span class="python_operator">= </span><span class="python_name">Table</span><span class="python_enclosure">(</span><span class="python_literal">'addresses'</span><span class="python_operator">, </span><span class="python_name">metadata</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'email'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">primary_key</span><span class="python_operator">=</span><span class="python_name">True</span><span class="python_enclosure">)</span><span class="python_operator">,</span>
    <span class="python_name">Column</span><span class="python_enclosure">(</span><span class="python_literal">'username'</span><span class="python_operator">, </span><span class="python_name">String</span><span class="python_enclosure">(</span><span class="python_number">50</span><span class="python_enclosure">)</span><span class="python_operator">, </span><span class="python_name">ForeignKey</span><span class="python_enclosure">(</span><span class="python_literal">'users.username'</span><span class="python_operator">, </span><span class="python_name">onupdate</span><span class="python_operator">=</span><span class="python_literal">"cascade"</span><span class="python_enclosure">)))</span><span class="python_operator">
</span>
<span class="python_keyword">class </span><span class="python_name">User</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span><span class="python_keyword">class </span><span class="python_name">Address</span><span class="python_enclosure">(</span><span class="python_name">object</span><span class="python_enclosure">)</span><span class="python_operator">:
    </span><span class="python_keyword">pass</span><span class="python_operator">
</span>
<span class="python_operator"></span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">User</span><span class="python_operator">, </span><span class="python_name">users</span><span class="python_operator">, </span><span class="python_name">properties</span><span class="python_operator">=</span><span class="python_enclosure">{</span>
    <span class="python_literal">'addresses'</span><span class="python_operator">:</span><span class="python_name">relation</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">passive_updates</span><span class="python_operator">=</span><span class="python_name">False</span><span class="python_enclosure">)</span>
<span class="python_enclosure">})</span><span class="python_operator">
</span><span class="python_name">mapper</span><span class="python_enclosure">(</span><span class="python_name">Address</span><span class="python_operator">, </span><span class="python_name">addresses</span><span class="python_enclosure">)</span><span class="python_operator">
</span></pre>
    </div>
<p>passive_updates is set to <code>True</code> by default.  Foreign key references to non-primary key columns are supported as well.
</p>




            <a href="#top" class="totoc">back to section top</a>
    </div>




    </div>




    </div>





    <div class="bottomnav">
        
    <div class="prevnext">

            
            Previous: <a href="sqlexpression.html">SQL Expression Language Tutorial</a>

               |   
            Next: <a href="session.html">Using the Session</a>
    </div>

    </div>






</body>
</html>






